Getting rid of char(160)
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 |
Getting rid of char(160)
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" wrote in message ... 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 |
Getting rid of char(160)
Thanks, Chip!
Selection.Replace what:=Chr(160) Worked! Biff "Chip Pearson" wrote in message ... 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" wrote in message ... 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 |
All times are GMT +1. The time now is 11:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com