Delete Last 10 Rows in Worksheet

[Sloth]

I need a macro that can delete the last ten rows in the worksheet. Column A
will always contain information on the last row, but has a lot of gaps in the
rest of the column.

 

[Mike H]

Maybe

Sub substance()
lastrow = Range("A65536").End(xlUp).Row
For x = lastrow To (lastrow - 9) Step -1
Rows(x).EntireRow.Delete
Next
End Sub

Mike

 

[Gary''s Student]

Sub hfdksjf()
n = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & n - 9 & ":A" & n).EntireRow.Delete
End Sub

 

[Jim Thomlinson]

You can do it with this line of code...

Sheets("Sheet1").Cells(Rows.Count, _
"A").End(xlUp).Offset(-9).Resize(10).EntireRow.Delete

 

[Jim Thomlinson]

65536 assumes that Sloth is not on XL2007 which has a lot more rows than
that. You might consider using rows.count to define the end of the sheet.

 

[Mike H]

Jim,

Thanks for the tip, I must get into th habit of doing that with the advent
of 2007 but in this case and from the OP's other thread I knew 65536 was
appropriate

http://www.microsoft.com/office/com...044b0d-14a6-48d8-99b4-6b4496d0ac15&sloc=en-us

Mike

 

[Jim Thomlinson]

Until he upgrades...
--
HTH...

Jim Thomlinson

Jim,

Thanks for the tip, I must get into th habit of doing that with the advent
of 2007 but in this case and from the OP's other thread I knew 65536 was
appropriate

http://www.microsoft.com/office/com...044b0d-14a6-48d8-99b4-6b4496d0ac15&sloc=en-us

Mike

 

[Sloth]

Thank You All.

You can do it with this line of code...

Sheets("Sheet1").Cells(Rows.Count, _
"A").End(xlUp).Offset(-9).Resize(10).EntireRow.Delete

 

[Sloth]

LOL, that'l be the day. I have to fight tooth and nail for every scrap I
get. I've been requesting a hard-drive upgrade for a while with no luck (I
am 70GB full of an 80GB drive).

I went ahead and used rows.count anyways, just in case . Thanks again.