How to select and delete blank cells

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. How to quickly select a cell range

Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy way to do it if you know the exact cell reference follow these simple steps.

1. Type the cell reference in the name box.
2. Press Enter.
3. The cell range is now selected.

Excel references cells using a column letter and a row number called A1 reference style, for example, the first cell at top left has the address A1.

2. How to quickly select a named range or an Excel table

You can also use the technique described in section 1.1 above to select named ranges and Excel defined tables. Simply type the name of the named range or the name of the Excel defined Table to quickly move and select to that object.

The animated picture above demonstrates how to select an Excel defined Table using the name box.

3. Select a contiguous cell range

If you don't know the exact cell reference to a table or list simply press with left mouse button on a non-empty cell in the range you want to select and press CTRL + A.

This will select the cell range as long as it is a contiguous cell range, the above animated picture shows this technique.

4. How to name a cell range

The name box allows you to quickly name a cell or cell range, this is useful if you often use a cell value in a formula.

Revenue2017 is easier to remember than cell reference AB153.

5. How to link to a specific cell range

Excel allows you to insert a hyperlink that points to a webpage, however, most people don't know that you can build a link that points to a cell range.

Here are the steps I made to build a hyperlink that selects cell range D3:F15:

1. Type Data in cell B2 and then press Enter.
2. Press with right mouse button on on cell B2 and select "Link"
   
3. Press with mouse on "Place in This Document"
   
4. Type D3:F15 in field "Type the cell reference:"
5. Press with left mouse button on OK button

The following post explains how to build a cell link using a formula that lets you instantly select a cell range or an Excel defined Table.

The advantage with formulas is that you can make them dynamic meaning they are instantly refreshed and updated if a cell value changes.

Recommended articles

Easily select data using hyperlinks
The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic […]

6. Different ways to 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

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

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

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

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

10.1 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

10.2 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

11. 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 replace part of formula in all cells
Table of Contents How to replace part of formula in all cells Substitute multiple text strings - Excel 365 recursive […]

Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They are called "print preview lines" and they […]

How to improve worksheet readability in Excel
How to make your worksheets useful? Making your sheets easy to read is a fundamental approach of creating useful worksheets. […]

Excel categories

Leave a Comment