VBA selction of rows using a variable

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

Hi All

After running my macro , I need to delete a number of rows from the end
of the worksheet.

I use the variable lrow to find the last row.

To delete the 20 rows below the last row , I'm trying this :

Rows("lrow+1:lrow+20").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp


It's not working , and gives errors. Can someone advise on a code that
would work?


Grateful for any advice.
 
Colin Hayes expressed precisely :
Hi All

After running my macro , I need to delete a number of rows from the end of
the worksheet.

I use the variable lrow to find the last row.

To delete the 20 rows below the last row , I'm trying this :

Rows("lrow+1:lrow+20").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp


It's not working , and gives errors. Can someone advise on a code that would
work?


Grateful for any advice.
Click to expand...

Try...

Rows(CStr(lrow + 1) & ":" & CStr(lrow + 20)).Entirerow.Delete
 
Another way:

Rows(lrow+1).resize(20).Delete

Hi All

After running my macro , I need to delete a number of rows from the end of the
worksheet.

I use the variable lrow to find the last row.

To delete the 20 rows below the last row , I'm trying this :

Rows("lrow+1:lrow+20").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp


It's not working , and gives errors. Can someone advise on a code that would work?


Grateful for any advice.
Click to expand...
 
Dave Peterson pretended :
Another way:

Rows(lrow+1).resize(20).Delete

On 02/18/2011 10:24, Colin Hayes wrote:
Click to expand...

Yep, that's how I'd do it! Just thought the OP needed to understand
what was needed in context of the posted code was that the address
needed to be a string expression.<g>
 
You can actually get away without using cStr():
Rows(lRow + 1 & ":" & lRow + 20).EntireRow.Delete

And since you're dealing with rows (which are entire rows):
Rows(lRow + 1 & ":" & lRow + 20).Delete

Excel's vba can be very forgiving.
 
Dave Peterson formulated on Friday :
You can actually get away without using cStr():
Rows(lRow + 1 & ":" & lRow + 20).EntireRow.Delete

And since you're dealing with rows (which are entire rows):
Rows(lRow + 1 & ":" & lRow + 20).Delete

Excel's vba can be very forgiving.
Click to expand...

Thanks, Dave. It almost didn't seem right to include EntireRow but I'm
in the habit of using it and so it came out. I did not know only the
list separator had to be wrapped in quotes. Nice to learn that!
 
Back
Top