4th Floor, 4 Tabernacle Street London EC2A 4LU

VBA – Remove Blank Rows

as below:

[vb]

Sub DeleteBlankRows1()

‘Deletes the entire row within the selection if the ENTIRE row contains no data.

 

‘We use Long in case they have over 32,767 rows selected.

Dim i As Long

 

‘We turn off calculation and screenupdating to speed up the macro.

With Application

.Calculation = xlCalculationManual

.ScreenUpdating = False

‘We work backwards because we are deleting rows.

For i = Selection.Rows.Count To 1 Step -1

If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

Selection.Rows(i).EntireRow.Delete

End If

Next i

 

.Calculation = xlCalculationAutomatic

.ScreenUpdating = True

End With

End Sub

[/vb]