2

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?

1 Answer 1

3

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
Next
End Sub
4
  • Thanks. Just out of interest, is there a non-VBA solution?
    – Aaa
    Oct 4, 2013 at 13:37
  • Thanks, for those interested, changing cell.Clear to cell.Value = "" will remove cell contents but keep formats intact.
    – Aaa
    Oct 4, 2013 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, 2013 at 13:50
  • Thank you nixda for your attention to this question. I appreciate it!
    – Aaa
    Oct 4, 2013 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.