ExcelBanter - View Single Post - Delete all rows below my data to the bottom of the sheet

Dan Winterton[_3_]

Works Great. Thanks.

"Bob Phillips" wrote:

LastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Rows(LastRow+1 & ":" & Rows.Count).Delete

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan Winterton" wrote in message
...
I know that you can't delete the rows. But the problem is that although
there is no data in those bottom rows, excel thinks that there is. When
you
try to add a row later, it tells you that it cannot "shift rows off the
worksheet." -- a common issue with excel.

The solution to this issue is to select all rows below your data, to the
bottom of the worksheet, and delete them, then re-save your document.
then,
excel will let you add rows.

All I want to do is use a macro to do the deletion for me. I think all I
need is a line of code that can count how many rows of data I have, then a
statement to select all rows from "last row + 1":65534 (the bottom of the
worksheet), and delete them.

Can anyone help with this? Thanks.

"Bob Phillips" wrote:

You cannot delete those rows, if you do so, Excel just fills it up with
new
rows. A worksheet has 65536 rows, period. But empty rows do not use
space
when the workbook is saved.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan Winterton" <Dan wrote in
message
...
I am dealing with large blocks of data, ranging from 10,000 to 20,000
rows.

Is there a simple way I can use code to delete all rows below my data,
down
to the very bottom of the spreadsheet? The # of rows will not be the
same
each time.

Any help is greatly appreciated.