Macro to delete blank rows in a worksheet

[adchris]

I am attempting to create a macro that will eliminate blank rows from a tab
in a worksheet. I need to do this in order to maintain the "auto-complete"
functionality.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Sonic()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Mike

 

[Gord Dibben]

Do you need a macro?

Select a column with the blank rows and F5>Special>Blanks>OK

Edit>Delete>Entire Row.


Gord Dibben MS Excel MVP