Substitute() & Char() in Macro
I am trying to write a macro to remove white space in my excel spreadsheet. I
have following script but it says my sub or function not defined, but if I use it in excel (not macro) it actually works. Any idea? Thanks. Sub WhiteSpaceRemover() Application.ScreenUpdating = False For Each cell In Selection cell.Value = Trim(Substitute(cell.Value, CHAR(160), CHAR(32))) Next cell End Sub |
Substitute() & Char() in Macro
Substitute is a worksheet function. Try
Worksheetfunction.Substitute. Or else try VBscript function REPLACE() If this post helps click Yes --------------- Jacob Skaria "Jeremy" wrote: I am trying to write a macro to remove white space in my excel spreadsheet. I have following script but it says my sub or function not defined, but if I use it in excel (not macro) it actually works. Any idea? Thanks. Sub WhiteSpaceRemover() Application.ScreenUpdating = False For Each cell In Selection cell.Value = Trim(Substitute(cell.Value, CHAR(160), CHAR(32))) Next cell End Sub |
Substitute() & Char() in Macro
Somthing like the below..Are you sure it is 160 or Chr(10)
For Each cell In Selection Cell.Value = WorksheetFunction.Trim(Replace(cell.Value, Chr(10), Chr(32))) Next cell If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Substitute is a worksheet function. Try Worksheetfunction.Substitute. Or else try VBscript function REPLACE() If this post helps click Yes --------------- Jacob Skaria "Jeremy" wrote: I am trying to write a macro to remove white space in my excel spreadsheet. I have following script but it says my sub or function not defined, but if I use it in excel (not macro) it actually works. Any idea? Thanks. Sub WhiteSpaceRemover() Application.ScreenUpdating = False For Each cell In Selection cell.Value = Trim(Substitute(cell.Value, CHAR(160), CHAR(32))) Next cell End Sub |
Substitute() & Char() in Macro
Hey Jacob,
Thanks for the solution, I was thinking to remove excessive space using chr(160) but you reminded me to remove the chr(10) too. Thanks. =) Jeremy "Jacob Skaria" wrote: Somthing like the below..Are you sure it is 160 or Chr(10) For Each cell In Selection Cell.Value = WorksheetFunction.Trim(Replace(cell.Value, Chr(10), Chr(32))) Next cell If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Substitute is a worksheet function. Try Worksheetfunction.Substitute. Or else try VBscript function REPLACE() If this post helps click Yes --------------- Jacob Skaria "Jeremy" wrote: I am trying to write a macro to remove white space in my excel spreadsheet. I have following script but it says my sub or function not defined, but if I use it in excel (not macro) it actually works. Any idea? Thanks. Sub WhiteSpaceRemover() Application.ScreenUpdating = False For Each cell In Selection cell.Value = Trim(Substitute(cell.Value, CHAR(160), CHAR(32))) Next cell End Sub |
All times are GMT +1. The time now is 05:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com