Excel: Delete/Deleting Blank Rows With Excel VBA

Conditional Excel Row Deleting-Delete Rows Based on Criteria

Below are 6 methods that will delete rows from within a selection.
If you know the range you can replace "Selection" with your Range().
It is important to note that the least efficient methods involve those that use
loops.
This is because they only delete one row at a time!

In some examples we turn off Calculation and Screenupdating. The reason we turn
off calculation is in case the range in which we are deleting rows contains lots
of formulas, if it does Excel may need to recalculate each time a row is deleted,
slowing down the macro. The screenupdating being set to false will also speed up
our macro as Excel will not try to repaint the screen each time it changes.

Subs: DeleteBlankRows1, DeleteBlankRows3 and both Worksheet_Change
events are slightly different as they first check to see if the ENTIRE row is blank.

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

Sub DeleteBlankRows2()
'Deletes the entire row within the selection if _
some of the cells WITHIN THE SELECTION contain no data.
On Error Resume Next
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Sub DeleteBlankRows3()
'Deletes the entire row within the selection if _
the ENTIRE row contains no data.
Dim Rw As Range
If WorksheetFunction.CountA(Selection) = 0 Then
MsgBox "No data found", vbOKOnly, "OzGrid.com"
Exit Sub
End If
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
Selection.SpecialCells(xlCellTypeBlanks).Select
For Each Rw In Selection.Rows
If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
Selection.EntireRow.Delete
End If
Next Rw
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Open Excel.
Push Alt+F11 to open the VBE (Visual Basic Editor).
Go to Insert>Module.
Copy the code and paste it in the new module.
Push Alt+Q to return to Excels normal view.
Push Alt+F8 and then select the macro name and click Run. Or select Options and
assign a shortcut key.

Removing Blank Rows Automatically

The codes above will work fine for removing blank rows from a list that already
has some, but as the saying goes "Prevention is better than cure". The two examples
below will remove blank rows as they occur. Either code should be placed within
the Worksheet module and will occur each time a cell changes on the worksheet.

In both codes you will notice the Application.EnableEvents=False this is often needed
within Event codes like this, else the Event will be triggered again once the code
executes which in turn will again trigger the Event and so on.....

You will no doubt also notice the GoTo SelectionCode which occurs if the number
of cells within the selection exceeds one. The reason for this is an error would
occur if the code reached the Target keyword as Target refers to a single cell.

The second example uses the Sort method rather than the EntireRow.Delete and is
the preferred method to use if possible. What happens is, any blank rows are placed
at the bottom of the range should the entire row be blank.

The use of the keyword Me is a good habit to get into when working within Worksheet
and Workbook modules. This was shown to me by my internet friend from Belgium, Geert
Dumortier.

Private Sub Worksheet_Change(ByVal Target As Range)
'Deletes blank rows as they occur.
'Prevent endless loops
Application.EnableEvents = False
'They have more than one cell selected
If Target.Cells.Count > 1 Then GoTo SelectionCode
If WorksheetFunction.CountA(Target.EntireRow) = 0 Then
Target.EntireRow.Delete
End If
Application.EnableEvents = True
'Our code will only enter here if the selection is more than one cell.
Exit Sub
SelectionCode:
If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
Selection.EntireRow.Delete
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Sorts blank rows to the bottom as they occur
'Prevents endless loops
Application.EnableEvents = False
'They have more than one cell selected
If Target.Cells.Count > 1 Then GoTo SelectionCode
If WorksheetFunction.CountA(Target.EntireRow) <> 0 Then
Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
Application.EnableEvents = True
Exit Sub 'Our code will only enter here if the selection is _
more than one cell.
SelectionCode:
If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
Application.EnableEvents = True
End Sub

To use either one of the above codes:

Open Excel.
Right click on the Sheet name tab.
Select View Code from the Pop-up menu
Copy the code and paste it over the top of the default Event
Push Alt+Q to return to Excels normal view.
Push Alt+F8 and then select the macro name and click Run. Or select Options and
assign a shortcut key.

Of all the examples above that use Excels AutoFilters and Sort are by far the quickest
methods I know of. If anybody knows of a quicker way, please tell me.