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 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

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

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