Find the Last row, and then delete all the blank rows from that row till the last row (1048576)

Question

HI There,

Im having file size issues, and I think its to do with a macro that copys and paste visible cells into another tab.  I found that when I delete all the "empty" rows (ie after my last line of data) that solves the problem.

As such I need some VB that will find my last row, and then select the very next row down to the last row, and delete those rows....

Ideas are welcomed!

Regards

Darin

Answer 1

Darin:

Here is a macro that you can use to delete empty rows.  Make sure you change the "C" in the example to your longest column.  There are other ways to determine the last data row (UsedRange.Rows.Count) but forum members have said that in rare instances, it may give results that are not exact.

So, here is the code:

Option Explicit

Public Sub DeleteEmptyRows()
Dim lngLastDataRow As Long
Dim strLongestColumn As String
Dim lngMaximumRows As Long

' *******************************************************
' Replace the "C" With The Column That Contains The
' Largest Number Of Rows
' *******************************************************
strLongestColumn = "C"

lngMaximumRows = ActiveSheet.Rows.Count
lngLastDataRow = ActiveSheet.Cells(Rows.Count, strLongestColumn).End(xlUp).Row

ActiveSheet.Rows(lngLastDataRow + 1 & ":" & lngMaximumRows).Delete

End Sub

You can run this macro using the ALT-F8 method, or assign it to a button.

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 2

Thank you so much - works perfectly (so far :) )