I need a macro that can delete the last ten rows in the worksheet. Column A
will always contain information on the last row, but has a lot of gaps in the
rest of the column.

Maybe

Sub substance()
lastrow = Range("A65536").End(xlUp).Row
For x = lastrow To (lastrow - 9) Step -1
Rows(x).EntireRow.Delete
Next
End Sub

Mike

"Sloth" wrote:

> I need a macro that can delete the last ten rows in the worksheet. Column A
> will always contain information on the last row, but has a lot of gaps in the
> rest of the column.

Sub hfdksjf()
n = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & n - 9 & ":A" & n).EntireRow.Delete
End Sub

--
Gary''s Student - gsnu200766


"Sloth" wrote:

> I need a macro that can delete the last ten rows in the worksheet. Column A
> will always contain information on the last row, but has a lot of gaps in the
> rest of the column.

You can do it with this line of code...

Sheets("Sheet1").Cells(Rows.Count, _
"A").End(xlUp).Offset(-9).Resize(10).EntireRow.Delete

--
HTH...

Jim Thomlinson


"Sloth" wrote:

> I need a macro that can delete the last ten rows in the worksheet. Column A
> will always contain information on the last row, but has a lot of gaps in the
> rest of the column.

65536 assumes that Sloth is not on XL2007 which has a lot more rows than
that. You might consider using rows.count to define the end of the sheet.
--
HTH...

Jim Thomlinson


"Mike H" wrote:

> Maybe
>
> Sub substance()
> lastrow = Range("A65536").End(xlUp).Row
> For x = lastrow To (lastrow - 9) Step -1
> Rows(x).EntireRow.Delete
> Next
> End Sub
>
> Mike
>
> "Sloth" wrote:
>
> > I need a macro that can delete the last ten rows in the worksheet. Column A
> > will always contain information on the last row, but has a lot of gaps in the
> > rest of the column.

Jim,

Thanks for the tip, I must get into th habit of doing that with the advent
of 2007 but in this case and from the OP's other thread I knew 65536 was
appropriate

http://www.microsoft.com/office/comm...c15&sloc=en-us

Mike

"Jim Thomlinson" wrote:

> 65536 assumes that Sloth is not on XL2007 which has a lot more rows than
> that. You might consider using rows.count to define the end of the sheet.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Mike H" wrote:
>
> > Maybe
> >
> > Sub substance()
> > lastrow = Range("A65536").End(xlUp).Row
> > For x = lastrow To (lastrow - 9) Step -1
> > Rows(x).EntireRow.Delete
> > Next
> > End Sub
> >
> > Mike
> >
> > "Sloth" wrote:
> >
> > > I need a macro that can delete the last ten rows in the worksheet. Column A
> > > will always contain information on the last row, but has a lot of gaps in the
> > > rest of the column.

Until he upgrades...
--
HTH...

Jim Thomlinson


"Mike H" wrote:

> Jim,
>
> Thanks for the tip, I must get into th habit of doing that with the advent
> of 2007 but in this case and from the OP's other thread I knew 65536 was
> appropriate
>
> http://www.microsoft.com/office/comm...c15&sloc=en-us
>
> Mike
>
> "Jim Thomlinson" wrote:
>
> > 65536 assumes that Sloth is not on XL2007 which has a lot more rows than
> > that. You might consider using rows.count to define the end of the sheet.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Mike H" wrote:
> >
> > > Maybe
> > >
> > > Sub substance()
> > > lastrow = Range("A65536").End(xlUp).Row
> > > For x = lastrow To (lastrow - 9) Step -1
> > > Rows(x).EntireRow.Delete
> > > Next
> > > End Sub
> > >
> > > Mike
> > >
> > > "Sloth" wrote:
> > >
> > > > I need a macro that can delete the last ten rows in the worksheet. Column A
> > > > will always contain information on the last row, but has a lot of gaps in the
> > > > rest of the column.

Thank You All.

"Jim Thomlinson" wrote:

> You can do it with this line of code...
>
> Sheets("Sheet1").Cells(Rows.Count, _
> "A").End(xlUp).Offset(-9).Resize(10).EntireRow.Delete
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Sloth" wrote:
>
> > I need a macro that can delete the last ten rows in the worksheet. Column A
> > will always contain information on the last row, but has a lot of gaps in the
> > rest of the column.

LOL, that'l be the day. I have to fight tooth and nail for every scrap I
get. I've been requesting a hard-drive upgrade for a while with no luck (I
am 70GB full of an 80GB drive).

I went ahead and used rows.count anyways, just in case . Thanks again.

"Jim Thomlinson" wrote:

> Until he upgrades...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Mike H" wrote:
>
> > Jim,
> >
> > Thanks for the tip, I must get into th habit of doing that with the advent
> > of 2007 but in this case and from the OP's other thread I knew 65536 was
> > appropriate
> >
> > http://www.microsoft.com/office/comm...c15&sloc=en-us
> >
> > Mike
> >
> > "Jim Thomlinson" wrote:
> >
> > > 65536 assumes that Sloth is not on XL2007 which has a lot more rows than
> > > that. You might consider using rows.count to define the end of the sheet.
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Mike H" wrote:
> > >
> > > > Maybe
> > > >
> > > > Sub substance()
> > > > lastrow = Range("A65536").End(xlUp).Row
> > > > For x = lastrow To (lastrow - 9) Step -1
> > > > Rows(x).EntireRow.Delete
> > > > Next
> > > > End Sub
> > > >
> > > > Mike
> > > >
> > > > "Sloth" wrote:
> > > >
> > > > > I need a macro that can delete the last ten rows in the worksheet. Column A
> > > > > will always contain information on the last row, but has a lot of gaps in the
> > > > > rest of the column.