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.
What's on this page
- Delete a value
- Delete a cell
- Select and delete blank cells
- Select and delete formulas returning a given condition
- Macro deletes formulas returning a blank
- Macro deletes entire row if the formula returns blanks
- Download excel *.xlsm file
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.
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.
Select and delete blank cells
- Select the cell range.
- Press function key F5 to open the following dialog box.
- Click "Special..." button and the following dialog box appears.
- Select radio button "Blanks", see picture above.
- Click "OK" button.
- Right-click on any of the selected cells and click "Delete..." on the pop-up menu.
- Click "Shift cells up" on the dialog box.
- Click OK button.
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.
- Go to tab "Data" on the ribbon.
- Click "Filter" button or press shortcut keys CTRL + SHIFT + L. Arrows next to the header names appears.
- Click on the arrow next to the header name Formulas and a pop-up menu appears.
- Deselect checkbox next to "Above" to filter blanks.
- 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.
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
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.
- Copy above VBA code.
- Press short cut keys Alt+ F11 to open the Visual Basic Editor (VB Editor).
- Click "Insert" on the top menu, see image above.
- Click "Module".
- Paste VBA code to code window.
- Exit VB Editor and return to Excel.
How to run the macro
- Select a cell range.
- Press Alt + F8 to open the macro dialog box.
- Select macro "DeleteBlankRows".
- Click Run.
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
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 […]
How to find errors in a worksheet
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
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 […]
Create a numbered list ignoring blank cells
The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
A number that is formatted as text will be left-aligned instead of right-aligned, this makes it easier for you to […]
How to quickly select blank cells
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]
How to add a macro to your Excel Quick Access Toolbar
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.