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.

Hi Dan,

Following should do the job, starting from your reference column

Sub DeleteRows()
Application.Goto Reference:="R65536C1"
Rows("65536:65536").Select
Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub

HTH
Cheers
Carim

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.

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.

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.

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.