True blanks

This about the problem of cells which appear to be blank and which are true blanks. If you check a cell using IsBlank() formula, you’d know if it’s a true blank or not.

Even if you copy paste special (values) this =””, there wouldn’t be anything in the cell and length of that cell would be zero – still it won’t be a blank. But if you press delete on that cell it would become a true blank.

Now, here is a macro to do the same thing, it would check if the cell has zero length and check if Istext is true, if both conditions are satisfied it would press a delete key on that cell.

Please check the Excel help on the functions- Len, Istext, and Isblank

To use this macro Press Alt + F11, double click the sheet you’re working on, copy and paste this code. Close VBA window.
To run it, select a range in the worksheet you want true blanks, press Alt + F8, select this macro and press Run.

‘macro to make a blank true blank
Sub make_true_blanks()
Dim c As Range
For Each c In Selection
If Len(c) = 0 And WorksheetFunction.IsText(c) Then
End If
MsgBox “Done”
End Sub

Please check this discussion on

About the Author

The author of Tableau Data Visualization Cookbook and an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.