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.

1. 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 press with left mouse button on "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

Press with mouse 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

2. Delete a single cell

To delete a single cell entirely (with or without a value) press with right mouse button on on it to show a pop-up menu or context menu, press with left mouse button on "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

3. Select and delete blank cells

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

Back to top

4. 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. Press with left mouse button on "Filter" button or press shortcut keys CTRL + SHIFT + L. Arrows next to the header names appears.
   
3. Press with mouse 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. Press with left mouse button on 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, press with left mouse button on "Special..." button.

Press with left mouse button on radio button "Visible cells only" and then press with left mouse button on OK button.

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

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

Back to top

5. 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. Press with left mouse button on "Insert" on the top menu, see image above.
4. Press with left mouse button on "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. Press with left mouse button on Run.

Back to top

6. 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

Get the Excel file

Delete-formulas-evaluating-to-an-empty-string.xlsm

Excel basics category

How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]

How to quickly select a non contiguous range
A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]

Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]

Excel categories

Leave a Comment