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

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

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

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