This article demonstrates how to select all blank cells in a given cell range and how to delete them. It also shows how to select cells containing formulas that return blank (nothing) and how to delete them as well.

I will also show how to build a VBA macro that deletes cells if empty and a macro that deletes the entire row if a cell is empty.

Delete a cell value

First, I want to show you how to delete a value in a cell. If you select the cell and then press the "Delete" key on the keyboard only the value or formula is deleted, not the cell formatting or the cell itself.

To delete the cell formatting as well you need to go to tab "Home" on the ribbon and click "Clear" button. A pop-up menu shows up containing these actions:

• Clear All
• Clear Formats
• Clear Contents
• Clear comments and notes
• Clear Hyperlinks
• Remove hyperlinks

Click on "Clear All" to delete the value and the cell formatting.

The shortcut keys to clear all are Alt + H + E + A. "Clear All" will remove the values, formulas, cell formatting, comments, notes, and hyperlinks from the selected cell or cell range.

Back to top

Delete a single cell

To delete a single cell entirely (with or without a value) right-click on it to show a pop-up menu or context menu, click "Delete...". A dialog box appears with the following options:

• Shift cells left - deletes the selected cell and move cells left to fill the deleted cell.
• Shift cells up - deletes the selected cell and move cells up to fill the deleted cell.
• Entire row - Deletes the entire row even if there are other non-empty cells on the same row. There is not even a warning, Excel deletes the entire row.
• Entire column - Deletes the entire column even if there are other non-empty cells in the same column. There is not even a warning, Excel deletes the entire column without a prompt.

You can use this method to delete all blank cells, see next section below.

Back to top

Select and delete blank cells

1. Select the cell range.
2. Press function key F5 to open the following dialog box.
   
3. Click "Special..." button and the following dialog box appears.
   
4. Select radio button "Blanks", see picture above.
5. Click "OK" button.
   
6. Right-click on any of the selected cells and click "Delete..." on the pop-up menu.
7. Click "Shift cells up" on the dialog box.
8. Click OK button.
   

Back to top

Select and delete cells matching a given condition

The image above shows a formula in column C that returns text string "Above" if the corresponding value in column B is above or equal to 50. If the number is below 50 then nothing is returned.

Formula in cell C3:

Excel is also able to select visible cells only, if we apply a simple filter to the table we can select the blanks and delete them. This allows you to filter cells based on any condition and then delete them.

1. Go to tab "Data" on the ribbon.
2. Click "Filter" button or press shortcut keys CTRL + SHIFT + L. Arrows next to the header names appears.
   
3. Click on the arrow next to the header name Formulas and a pop-up menu appears.
   
4. Deselect checkbox next to "Above" to filter blanks.
5. Click OK button.

The row numbers and the arrow button shows that a filter is applied to the data, see image above. Select all blanks, in my example that would be cell C3, C4, C7, C9 and C10.

Press Function key F5 on your keyboard, click "Special..." button.

Click radio button "Visible cells only" and then click OK button.

Click "Delete row" on the pop-up menu, see image above. This will delete all rows containing a blank in column C.

Click on the button next to the header name "Formulas" and a pop-up menu shows up. Click on 'Clear Filter From "Formulas"', this will show whats left of data table.

Back to top

Macro deletes formulas that return a blank

If you don't want to use the above technique or perhaps can't, the following macro deletes formulas evaluating to an empty text string:

'Name macro
Sub DeleteBlankRows()

'Iterate through selected cells
For i = Selection.Cells.Count To 1 Step -1

'Check if cell length is zero
   If Len(Selection.Cells(i)) = 0 Then 

      'Delete cell
      Selection.Cells(i).Delete xlUp

   End If

'Next cell
Next i
End Sub

Back to top

Where to put the VBA code?

The following steps shows how to add VBA code to your workbook. VBA stands for Visual Basic for Applications and it allows you to create custom-built programs or Excel Functions.

1. Copy above VBA code.
2. Press short cut keys Alt+ F11 to open the Visual Basic Editor (VB Editor).
3. Click "Insert" on the top menu, see image above.
4. Click "Module".
5. Paste VBA code to code window.
6. Exit VB Editor and return to Excel.

Back to top

How to run the macro

1. Select a cell range.
2. Press Alt + F8 to open the macro dialog box.
3. Select macro "DeleteBlankRows".
4. Click Run.

Back to top

Macro deletes entire row if the formula returns blanks

This macro deletes the entire row if cell formula evaluates to an empty text string:

'Name macro
Sub DeleteBlankRows()

'Go through all selected cells 
For i = Selection.Cells.Count To 1 Step -1

   'Check if cell length is zero
   If Len(Selection.Cells(i)) = 0 Then 

      'Delete entire row
      Selection.Cells(i).EntireRow.Delete xlUp

   End if

Continue with next cell
Next i
End Sub

Back to top

Weekly Blog EMAIL

Email

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---