Remove Unprintable Non-Blank Cells Having Zero Length Contents

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

It was imported from a csv.

Any ideas?

[patel]

attach please a sample file

[aaronb]

Sample workbook attached

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.

1072

[patel]

I did not understand your goal

[aaronb]

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.

[Rick Rothstein]

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.

[Rick Rothstein]

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/trim-all-cells-in-a-worksheet-vba-155/#post1092

[aaronb]

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.

[patel]

attach please a new file again with data and desired result

[Rick Rothstein]

attach please a new file again with data and desired result

I do not see any attachment. :confused:

[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(xlCellTypeConst ants)
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(xlCellTypeConst ants)
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