Deleting spaces from blank cell in sheet?

[tonywatsonhelp]

Hi Everyone.
I need a macro that can help me clean up my data range.
We export data from our CRM system which is very old and the data needs sorting out before i can do anything with it.
I've done most of the macros but am stuck and looking for help.

I need a macro to help me empty out cells that are supposed to be blank.
Some of the blank cells have spaces or invisible data in them (what i mean is there no databut they don't register as "" when i do my formulas)
So what i'm hoping for is some way to have a macro make all cells that have a space but nothing else in them empty.
please help if you can.

Thanks
Tony

 

[alansidman]

Option Explicit


Sub Clean()
Dim c As Range, rng As Range
Set rng = Range("A1:B10") 'Set your own range
For Each c In rng
c = Trim(c)
Next c
End Sub

 

[Fluff]

If you put this in an unused cell & point it to one of the "blank" cells, what does it say?
=CODE(a1)

 

[tonywatsonhelp]

Hi Fluff,
it came back with 160
Thanks
Tony

 

[Fluff]

In that case try

Sub tonywatson()
   ActiveSheet.UsedRange.Replace Chr(160), "", xlWhole, , , , False, False
End Sub

 

[tonywatsonhelp]

Thank you Fluff,
Seams to hsve done the job nicely
Tony

 

[Fluff]

You're welcome & thanks for the feedback