Delete all blank cells that are not empty

[OldManExcellor]

Is there any way I can find all blank cells that are not empty and delete them?



I use a pivot table with count. The count in pivot table counts blank cells that are somehow not empty. This makes the count pretty useless.

 

[Weaver]

Have the offending cells got formulas returning blank results?

 

[OldManExcellor]

They had. They were created using an =iferror(primary result, ""). Then I pasted the output from these formulas as values into the column where I now have non-empty blanks.

 

[Weaver]

If you don't mind deleting all thos formulas, then try

Sub test()
    Set r = ActiveSheet.UsedRange
    Set f = r.Find(what:="", lookat:=xlWhole, LookIn:=xlValues)
    If Not f Is Nothing Then
        fa = f.Address
        Do
            f.ClearContents
            Set f = r.FindNext(f)
        Loop Until f.Address = fa
    End If
End Sub

Before I ran this I had 'blank' cells but specialcells(xlcelltypeblanks) didn't pick anything up, afterwards, it did.

PS pivot tables are a pain when it comes to dealing with blank cells. Maybe you could try forcing the cells to error instead, see what effect that has.