Remove space

  • Thread starter Thread starter Jesse Braswell
  • Start date Start date
J

Jesse Braswell

I have:
Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

done this macro and write VBA.
But for some reason I can not seem to figure out why it will not remove
the space at the end of my number.
$3,000<space>
It will work on regular text but not on my number.

I did try to format the colum to text but still will not remove space.
Anyone have any ideas?

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **
 
I have:
Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

done this macro and write VBA.
But for some reason I can not seem to figure out why it will not remove
the space at the end of my number.
$3,000<space>
It will work on regular text but not on my number.
Click to expand...

IsNumeric(cell.Value) evaluates to TRUE when you have a number in the cell or
any expression that can be evaluated as a number. See HELP for the definition
of IsNumeric.


--ron
 
Ron told you how isnumeric() treats anything that looks like a number as a
number.

but you could use the the worksheet function: =IsNumber(). It's much less
forgiving:

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not Application.IsNumber(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next cell
End Sub

And I bet you were checking for a formula with this portion:
InStr(cell.Formula, "=") = 0

But you can check directly:

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not Application.IsNumber(cell.Value) And _
Not cell.HasFormula _
Then cell.Value = Application.Trim(cell.Value)
Next cell
End Sub
 
Back
Top