Remove Unprintable Non-Blank Cells Having Zero Length Contents

[Excel Fox]

I understand this may be a memory intensive code loop, but it worked for me with your sample file

Sub ClearNonLenCells()

    Dim C As Range
    For Each C In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
        If Len(C) = 0 Then
            C.ClearContents
        End If
    Next C
    
End Sub

[Rick Rothstein]

I understand this may be a memory intensive code loop, but it worked for me with your sample file

Where did you see the sample file at? I do not see attachments for this thread... do I have a setting wrong or did you use the file the OP posted yesterday in Message #3?

[Excel Fox]

#3 Rick

[Rick Rothstein]

I understand this may be a memory intensive code loop, but it worked for me with your sample file

Sub ClearNonLenCells()

    Dim C As Range
    For Each C In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
        If Len(C) = 0 Then
            C.ClearContents
        End If
    Next C
    
End Sub

It looks like the empty cell is coming in as the text value "" instead of actually being empty. You can see this by adding the one formula the OP did not try...

=ISTEXT(A1)

I think this will fix the file also (should be less memory intensive than your macro)...

Sub FixFile()
  Dim Ar As Range
  On Error GoTo NoText
  For Each Ar In Cells.SpecialCells(xlConstants, xlTextValues)
    Ar.Value = Ar.Value
  Next
NoText:
End Sub

[aaronb]

Just tried your macro Rick. Worked a treat. Thanks