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
- How to quickly select a cell range
- How to quickly select a named range or an Excel table
- How to quickly select a contiguous cell range
- How to name a cell range
- How to link to a specific cell range
- Different ways to delete a cell value
- Different ways to delete an entire 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
- Get excel *.xlsm file
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.
- Type the cell reference in the name box.
- Press Enter.
- 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:
- Type Data in cell B2 and then press Enter.
- Press with right mouse button on on cell B2 and select "Link"
- Press with mouse on "Place in This Document"
- Type D3:F15 in field "Type the cell reference:"
- 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
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.
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.
8. Select and delete blank cells
- Select the cell range.
- Press function key F5 to open the following dialog box.
- Press with left mouse button on "Special..." button and the following dialog box appears.
- Select radio button "Blanks", see picture above.
- Press with left mouse button on "OK" button.
- 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.
- Press with left mouse button on "Shift cells up" on the dialog box.
- Press with left mouse button on OK button.
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.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on "Filter" button or press shortcut keys CTRL + SHIFT + L. Arrows next to the header names appears.
- Press with mouse on the arrow next to the header name Formulas and a pop-up menu appears.
- Deselect checkbox next to "Above" to filter blanks.
- 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.
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
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.
- Copy above VBA code.
- Press short cut keys Alt+ F11 to open the Visual Basic Editor (VB Editor).
- Press with left mouse button on "Insert" on the top menu, see image above.
- Press with left mouse button on "Module".
- Paste VBA code to code window.
- Exit VB Editor and return to Excel.
10.2 How to run the macro
- Select a cell range.
- Press Alt + F8 to open the macro dialog box.
- Select macro "DeleteBlankRows".
- Press with left mouse button on Run.
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
Excel basics category
Table of Contents How to replace part of formula in all cells Substitute multiple text strings - Excel 365 recursive […]
Have you ever wondered how these lines got there on a worksheet? They are called "print preview lines" and they […]
How to make your worksheets useful? Making your sheets easy to read is a fundamental approach of creating useful worksheets. […]
Excel categories
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.