Tom’s Tutorials For Excel: Delete Rows With Blank Cells

Tom’s Tutorials For Excel: Delete Rows With Blank Cells

Sometimes you have a list for which you need to delete rows where a cell is blank in a particular column. If the list is small, you can eyeball each blank cell, select it, and delete the row manually, but what if your list is thousands of rows deep, and there are several hundred blank cells?

There is an easy way to handle this. The picture shows a Before and After example of how you need to delete rows having blank cells in column A.

Step 1
Press Alt+F11 to go to the Visual Basic Editor.

Step 2
Press Ctrl+G to open the Immediate window in the Visual Basic Editor.

Step 3
In the Immediate window type the command
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
and then, press the Enter key.
Note, Columns(1) refers to column A which to Excel is column 1. If the blank cells were in, say, column E, the command line would read Columns(5) because E is the fifth column from the left on the worksheet grid.

Step 4
Exit the Visual Basic Editor and return to your worksheet by pressing Alt+Q.