Sometimes you may think a cell is blank because there’s nothing in it visible. But turns out it may not be actually blank. There might be a formula that returns a null value, or maybe a hard space, newline, or just a simple space. However, this tutorial will teach you how to detect and delete blank cells that are not really blank in Excel.
How to Deal with Blank Cells That Are Not Really Blank in Excel: 4 Ways
1. Use Paste Special to Delete the Blank Cells That Are Not Really Blank in Excel
In the following screenshot, we have a dataset whose Profit column displays the profit amount only if the Income is greater than the expenditure. So the blank cells represent that there’s no profit but losses.
If you click any of the blank cells, you will see the corresponding formula in the formula bar.
Now to delete all the blank cells that are not actually blank,
❶ Select the entire column.
❷ Then press the CTRL + C keys simultaneously to copy the selected cells.
❸ Now right-click on the top cell of the Profit column.
❹ From the pop-up menu, choose Values (V).
This will replace all the formulas with their corresponding values. So the formulas that breed null values will be replaced by null values. This means those blank cells are not actually blank.
To verify this, click any one of those blank cells. In the formula bar, you will see no contents in them. They are literally blank now.
2. Apply Filter to Delete the Blank Cells That Are Not Really Blank in Excel
In this method, we will filter out all the blank cells that are not really blank and later delete them all.
To do that,
❶ Click anywhere on the dataset.
❷ Then press CTRL + SHIFT + L to apply Filter.
❸ Click on the drop-down icon of the Profit column.
❹ Select only the Blanks as shown and hit OK.
This will filter out all the blank cells that are not really blank.
❺ Now select them all.
❻ Then press CTRL + – keys together to delete them instantly.
A little dialog box will appear to get the confirmation regarding the deletion.
❼ Just hit the OK button.
❽ After that click on the Filter icon at the right-bottom corner of the Profit column.
❾ Select all the values and hit OK.
This will bring back all the cells with no blank cells among them.
3. Use Find and Replace to Remove the Blank Cells That Are Not Really Blank in Excel
Using this method, you can find all the blank cells that are not really blank. Later you can delete them altogether and make them actually blank.
To do that,
❶ Select the cells where you want to find the existence of the blank cells that are not really blank.
❷ Then press CTRL + H keys together.
The Find and Replace dialog box will appear.
❸ In the Find what box click once and then,
- Press the Space button once to look for the normal spaces.
- Press the ALT key and then type 0160 to find the hard spaces.
❹ After that, click on the Find All button.
This will create a list of all the records having spaces in any of the cells of them.
❺ Press CTRL + A to select all the search results.
❻ Leave the Replace with the box as it is.
❼ After that hit the Replace All button.
This command will delete all the blank cells that are not really blank.
4. Use VBA Script to Remove the Blank Cells That Are Not Really Blank but Newlines in Excel
It’s hard to determine if any of the blank cells contain spaces due to newlines. To remove them all, you need a VBA script.
❶ At first, press ALT + F11 to open the VBA editor.
❷ After that, create a new module from Insert > Module.
❸ Copy the following code.
Sub RemoveNewLines()
With Me.Cells
Set mm = .Find(Chr(10), LookIn:=xlValues, LookAt:=xlWhole)
If Not mm Is Nothing Then
ff = mm.Address
Do
mm.Value = ""
Set mm = .FindNext(mm)
If mm Is Nothing Then Exit Do
Loop While mm.Address <> ff
End If
End With
End Sub❹ Paste and save the code in VBA editor.
❺ Finally, press F5 to run the code.
This code will delete all the blank cells that are not actually blank but contains newlines.
Read More: How to Make Empty Cells Blank in Excel
Things to Remember
- Press CTRL + – keys to delete any items.
- To insert a hard space, press the ALT key and then type 0160.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 4 ways to delete blank cells that are not really blank in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP. And please visit our website Exceldemy to explore more.
Hi,
I get a compile error: invalid use of the Me keyword.
Any ideas?
Hi Joris,
The Me keyword can’t appear in a standard module because a standard module doesn’t represent an object. If you copied the code in question from a class module, you have to replace the Me keyword with the specific object or form name to preserve the original reference.
Thanks!