Getting rid of char(160)

[Biff]

Hi Folks!

I recorded a macro to clean the range selected of char(160)'s and attached
it to a button on one of my toolbars.

It's not working. Here's the code from the macro recorder:

Sub Clean160()

Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

The actual recorded code included as the first line:

Range("A1:A10").Select

I figured since that referenced a specific range I could just remove that
line and since the next line starts with Selection, that referred to any
range selected. Is that the problem?

Also, how does the code:

Selection.Replace What:=" ",

know that " " is char(160) and not char(32)?

I tried replacing =" " with =char(160) but that errored.

Thanks!

Biff

 

[Chip Pearson]

Biff,

Try
Selection.Replace what:=Chr(160), replacement:=" "

or in the Replace dialog box, hold down the ALT key and type 0160
on the numeric keypad (not the number keys above the letters).



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Biff]

Thanks, Chip!

Selection.Replace what:=Chr(160)

Worked!

Biff