VBA Snippets to Delete Rows in Excel

I regularly use simple macros to delete blank rows in my excel spreadsheets and there are loads of examples on internet forums.

Screen shot 2009-09-05 at 15.59.57

I’ve recently had to recreate these delete row macros to install on a new computer and thought it would be useful to keep them filed here on the blog.

This first macro will delete any row in which the cell in the selected column is blank. So in this example, with column B selected, rows 4 & 7 will be deleted.

1
2
3
4
5
6
7
Sub DeleteRowOnBlankCell()
'Deletes entire row within the selected column where cell within selection is blank
Select column then run the macro
On Error Resume Next
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

The following macro will only delete rows if the entire row is blank. Just select the area you want to evaluate and run the macro.

1
2
3
4
5
6
7
8
9
10
Sub DeleteBlankRows()

'Deletes the entire row within the selection provided the ENTIRE row is blank.
Dim i As Long
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
End Sub

To use these macros just copy and paste them into a module within your personal macro workbook (press ALT F11 to open the VBA editor) which will make them available for use whenever excel is open. Sometimes the personal workbook doesn’t show up in the VBA editor and if this is the case I just record a new macro, select the option to save it in the personal workbook, make a few changes to a spreadsheet and stop recording. This will have the effect of creating a new module in  personal.xls, along with a fairly useless macro that can now be deleted.

Share the Love
Get Blog Updates

Excel

Excel