Delete all rows below my data to the bottom of the sheet

[Guest]

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.

 

[Carim]

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

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

Works Great. Thanks.

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)