Selecting AND Deleting

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?

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

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

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.



"Gary''s Student" wrote:

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

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

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

--
Gary''s Student


"bodhisatvaofboogie" wrote:

> 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.
>
>
>
> "Gary''s Student" wrote:
>
> > 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
> >
> > --
> > 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?
> > >
> > >

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

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

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

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

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

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

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

"Gary''s Student" wrote:

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