VBA selction of rows using a variable

[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.

 

[GS]

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.

Try...

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

 

[Dave Peterson]

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.

 

[GS]

Dave Peterson pretended :

Another way:

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

On 02/18/2011 10:24, Colin Hayes wrote:

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>

 

[Dave Peterson]

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.

 

[GS]

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.

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!