Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "S").End(xlUp).Row
If lastrow < 11 Then Exit Sub
Set r = Range(Cells(2, "S"), Cells(lastrow - 10, "S"))
r.EntireRow.Delete

This seemed to work for me. It goes to the bottom of the cells in a row,
moves up 10, then selects and deletes everything above that up to row 2,
leaving the header row in tact. This has worked for me thus far with no
errors.

"Bob Phillips" wrote:

I'll bet that it is caused because the lastrow is not greater than 10, so
the range is invalid.

What should happen if lastrow is say 10, which rows should it delete? Ditto
lastrow = 6?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bodhisatvaofboogie" wrote in
message ...
It goes into Debug and highlights this line:

Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))

So I'm not sure what is wrong with it, but it doesn't work like I want it
to
:)


"Bob Phillips" wrote:

Your code should work. What are you seeing?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bodhisatvaofboogie"
wrote in
message ...
What is a good formula for Deleting a specific range of rows? I am
trying
to
figure out a way to delete all but the last 10 rows from a large set.
I'm
implementing it into a macro, and am struggling with how to get it
done.
I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!