improve DeleteBlankRows()

[Bucky]

I was using Pearson's DeleteBlankRows() function, but I found a minor
issue. The problem is that if your sheet has blank rows at the top,
they don't get deleted. I believe the problem is that

Set Rng = ActiveSheet.UsedRange.Rows

does not include the blank rows at the top (since they are not
considered "UsedRange"). I think the fix is to modify the Set Rng line
to include those blank rows. I'm not familiar with the objects and
properties, so can someone help me come up with the most elegant way to
do this? Conceptually, this is what I want to do:

Set Rng = ActiveSheet.(A1 to LastCellOfUsedRange).Rows


http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows

 

[Ron de Bruin]

Hi Bucky

Try this one


Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To 1 Step -1

If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the whole row is empty (all columns)

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

 

[Bucky]

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

Thanks, I basically used that idea. But I wanted to keep the rest of
the code intact, so this is the section that sets the range from A1 to
the last used cell.

lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
lastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
Set Rng = ActiveSheet.Range(Cells(1, 1), Cells(lastRow, lastCol))