Selecting AND Deleting

[Guest]

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?

 

[Guest]

1. make a range with cells in each row you want to delete and then use
Entire.Row


Sub Macro1()
Dim r As Range
Set r = Range("A1:A9")
r.EntireRow.Delete
End Sub

 

[Guest]

Well the range of rows is not always going to be the same. Entering a set
range will make all future imported data not format properly. I'll take that
and see if I can add the LastRow function into a range. That is what I
haven't figured out yet. That way regardless of how many rows pop into
the document, it will be formated based on the last row -9 and effectively
delete what I want gone. Thanks for the input.

 

[Guest]

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

 

[Guest]

To leave 10 rows:

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 - 10, "v"))
r.EntireRow.Delete
End Sub

 

[Guest]

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?

 

[Guest]

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

Make sure its not 0 for any reason.

 

[Guest]

I'm not sure I understand what you mean.

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

Make sure its not 0 for any reason.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

That worked great, THANKS!!! got it figured out now and working well.