VBA selction of rows using a variable

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

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
 
D

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

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>
 
D

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

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top