Is there a way to make supposedly blank cells truly blank?

I have had a bunch of formulae evaluate to "", whereafter I proceeded to copy the results and paste values. Now when I check for blanks using ISBLANK(A1) or IF(A1="",TRUE,FALSE) I get FALSE, unless I select the pseudo-blank cell and press delete first.

There are quite a few of these cells so I tried using F5 (Go To Special) but none of the options (blank, constants, etc.) give the appropriate selection of the pseudo-blank cells.

How do I locate all these pseudo-blank cells in one go and delete them so that I can obtain truly blank cells?


Quick VBA routine.
Looks through every Used cell. If cell is not empty but the value is a nullstring then clear this cell.

Sub clearEmptyValues()
For Each cell In UsedRange
    If Not IsEmpty(cell) And cell.Value = vbNullString Then cell.Formula = vbNullString
End Sub
  • Thanks. Just out of interest, is there a non-VBA solution? – Aaa Oct 4 '13 at 13:37
  • Thanks, for those interested, changing cell.Clear to cell.Value = "" will remove cell contents but keep formats intact. – Aaa Oct 4 '13 at 13:43
  • 1
    See my edit. Now it clears only the formula and leaves formatting in tact. And your solution does also work. Regarding your first question: I doubt there is a non-VBA solution – nixda Oct 4 '13 at 13:50
  • Thank you nixda for your attention to this question. I appreciate it! – Aaa Oct 4 '13 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.