VBA Substitute Chr 160

[Shind1]

Hi,

I can get rid of Char 160 using SUBSTITUTE(A1,CHAR(160),"") in Excel, but would like todo this in VBA. So I have the below code but its doesn't seem to work.
For Each A In Range("B15:B" & Range("B65536").End(xlUp).row)

ID = A.Value
ID = Application.WorksheetFunction.Substitute(ID, Chr(160), "")
A.Value = ID

Next A

Please can anybody assist.

Thanks

 

[Rick Rothstein]

Hi,

I can get rid of Char 160 using SUBSTITUTE(A1,CHAR(160),"") in Excel, but would like todo this in VBA. So I have the below code but its doesn't seem to work.
For Each A In Range("B15:B" & Range("B65536").End(xlUp).row)

ID = A.Value
ID = Application.WorksheetFunction.Substitute(ID, Chr(160), "")
A.Value = ID

Next A

Please can anybody assist.

Thanks

Try this...

Sub Remove160s()
    Columns("B").Replace Chr(160), "", xlPart
End Sub

 

[Shind1]

Hi,

Tried this still doesn't work unfortunately.

Thanks

 

[Rick Rothstein]

Tried this still doesn't work unfortunately.

It worked on the tests I did on my computer when I tried it before posting it back to you. Your data with the Chr(160) characters is in Column B (as your originally posted code indicates it is), correct? Describe "still doesn't work" for us (for example, is there an error, if so, which one?) so we have some idea what you see when you try the code.

 

[pelan]

Worked like a champ for me. Thanks!!

Darrell

Try this...

Sub Remove160s()
    Columns("B").Replace Chr(160), "", xlPart
End Sub