Macro to remove invisible characters

[VegaOne16]

Hello,

The issue I am having is when I export data from SAP as an excel workbook, cells that are blank are showing up with invisible characters of some sorts. I know this is happening because the exported workbooks are about 80mb in size when they should be about 1mb. If I use the ISBLANK() function and reference a cell that should be blank, it's returning an answer of "FALSE". If I use F2 on the Keyboard and then hit ENTER, the result of the ISBLANK will then be true. I can also clear the cell contents using the keyboard shortcut "ALT-E-A-A" and ISBLANK will also show up as true. If I 'clear' one column of characters, my workbook size decreases by 1mb or so.

I was hoping someone could help me with a macro that would remove the invisible characters. I don't know how to research what they are... but my suggestion would be a loop macro that does something like this:

Step 1 - Go to Cell A1
Step 2 - If ISBLANK(A1)=TRUE, to go Cell A2, if not then go to step 3.
Step 3 - If LEN(A1)=0, perform "Alt-E-A-A" to clear the cell contents, then go to Cell A2 and reperform.
Step 4 - Loop until all cells in sheet are analyzed

For additional ideas, under an Oracle platform this issue existed and the following macro eliminated those characters, however it doesn't seem to work on data exported from SAP:

Sub RemoveInvisibleCharacters()

Cells.Replace What:=ChrW(&H202D), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
MsgBox "Invisible Buggers Gone!"

End Sub

I'm using Windows 10 with Office 365.

Thank you for your help.

 

[Michael M]

Ok, a better method...

Sub MM1()
For Each c In ActiveSheet.UsedRange
c.Replace Chr(160), "", xlPart
Next c
End Sub

BTW if you received a #Value error using the code formula, it means the cell as blank
=CODE(AI65623)
Di you try it on a cell within the data set ??