Remove Unprintable Non-Blank Cells Having Zero Length Contents

Hi,

I'm importing data into a spreadsheet and what are supposed to be blank cells actually aren't. ISBLANK returns false. CODE(cellref) returns #VALUE error -- in the calculation steps, it looks like CODE( ""). TRIM(cellref) doesn't return a blank cell. Neither does CLEAN(cellref).

"Clear contents" works.

It was imported from a csv.

Any ideas?

attach please a sample file

Sample workbook attached

Attachment 1070

Edit: The cell didn't copy correctly -- ISBLANK returns TRUE. Let me try to get a disfunctional one up.

Sorry about that, here's a proper copy. The messed-up cell is A1.

Attachment 1072

I did not understand your goal

Quote:

---

Originally Posted by patel

I did not understand your goal

---

Macro to get rid of that character in the workbook so the affected cells return true for an ISBLANK check.

Quote:

---

Originally Posted by aaronb

Hi,

I'm importing data into a spreadsheet and what are supposed to be blank cells actually aren't. ISBLANK returns false. CODE(cellref) returns #VALUE error -- in the calculation steps, it looks like CODE( ""). TRIM(cellref) doesn't return a blank cell. Neither does CLEAN(cellref).

"Clear contents" works.

---

Give this a try and see if it fixes your problem. Select all the cells on the worksheet, then press CTRL+H to bring up Excel's Replace dialog box. If there is anything in the "Find what" field, remove it, then click into the "Find what" field to place the text cursor in it, then press and hold down the ALT key and, while it is down, type the numbers 0160 using the NUMBER PAD only (it will not work if you use the main keyboard to input those digits). Make sure there is nothing in the "Replace with" field. Click the "Options>>" button and make sure the "Match entire cell contents" checkbox does NOT have a check mark in it, then click the "Replace All" button. See if you can do whatever you want/need with those cells now.

Quote:

---

Originally Posted by aaronb

Macro to get rid of that character in the workbook so the affected cells return true for an ISBLANK check.

---

If you want a macro, then check out the code I posted in this message in different forum...

http://www.excelfox.com/forum/f13/tr...-155/#post1092

Quote:

---

Originally Posted by Rick Rothstein

Give this a try and see if it fixes your problem. Select all the cells on the worksheet, then press CTRL+H to bring up Excel's Replace dialog box. If there is anything in the "Find what" field, remove it, then click into the "Find what" field to place the text cursor in it, then press and hold down the ALT key and, while it is down, type the numbers 0160 using the NUMBER PAD only (it will not work if you use the main keyboard to input those digits). Make sure there is nothing in the "Replace with" field. Click the "Options>>" button and make sure the "Match entire cell contents" checkbox does NOT have a check mark in it, then click the "Replace All" button. See if you can do whatever you want/need with those cells now.

---

Rick,

Unfortunately that didn't work. It did return 4 matches, but not the cells in question.

attach please a new file again with data and desired result

Quote:

---

Originally Posted by patel

attach please a new file again with data and desired result

---

I do not see any attachment. :confused:

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