Remove Unprintable Non-Blank Cells Having Zero Length Contents

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

Code:

---

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

---

Quote:

---

Originally Posted by Excel Fox

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?

#3 Rick

Quote:

---

Originally Posted by Excel Fox

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

Code:

---

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)...

Code:

---

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

---

Just tried your macro Rick. Worked a treat. Thanks