The message is telling us that Excel thinks that there is only one row
available (that only V1 has data and there is nothing else in the column).


This means that there are no rows that can be deleted! For example, if
lastrow was 11 then the macro could delete the first row and still leave 10
rows. If lastrow was 12 then the macro could delete the first two rows, etc.


In other words, rows can only be deleted if there are more than 10. Here is
a version of the macro that will delete extra rows if it can and not go into
error mode:


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

--
Gary''s Student


"bodhisatvaofboogie" wrote:

The message box came up as a 1 then it stopped the macro and went into
debug. So what next?

"Gary''s Student" wrote:

Because I can't see what you have in column V, I can't determine why the code
stops. In this form:

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

the code will not delete the last 10 lines, but lastrow must be 11 or greater.
--
Gary's Student


"bodhisatvaofboogie" wrote:

I'm not sure I understand what you mean.

"Gary''s Student" wrote:

Let's MSGBOX(lastrow) right before the Set statement.

Make sure its not 0 for any reason.
--
Gary''s Student


"bodhisatvaofboogie" wrote:

It is stopping me at this line:

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

I've been trying variations of that line myself, and had no luck. What am I
missin?



"Gary''s Student" wrote:

If you want to leave only the last row:


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

--
Gary''s Student


"bodhisatvaofboogie" wrote:

I am working on More macros and I am trying to figure out how to Select Every
row except for the last 10. SO:

Select rows 1 through Last Row - 9.
Delete selected rows

LastRow = Cells(Rows.Count, "V").End(xlUp).Row
Cells(LastRow + 2, "V") = Application.Sum _
(Range(Cells(1, "V"), Cells(LastRow, "V")))

That is what I am using in another macro for selecting the last row +2 in
Column V, then summing the entire column within it. NOW, I figured I could
take that and pull a last row -9 from it and having it delete the entire row,
but I'm having some poor luck. I've managed to get it to delete everything
within the Column, but I can't seem to figure out how to make it get rid of
the rows.

Any ideas?