Macro to remove space at front and end of a cell
 

Similar to the TRIM() function but I would like to find a more automated way..

Thanks!

Macro to remove space at front and end of a cell
 

There are two TRIM functions. In the worksheet TRIM removes leading and
trailing spaces. It also removes "extra" internal spaces.

In VBA, TRIM leaves the "internal" spaces alone. If you want to do the
automatic worksheet-style TRIM, then select the cells and run:

Sub dural()
For Each r In Selection
a = r.Address
r.Value = Evaluate("Trim(" & a & ")")
Next
End Sub

This will convert the values in-place
--
Gary''s Student - gsnu200770


"yhoy" wrote:

Similar to the TRIM() function but I would like to find a more automated way..

Thanks!

Macro to remove space at front and end of a cell
 

If it is possible you may have any formulas in the selected range you mat want
to protect them from being wiped out.

Sub dural_hematoma()
For Each R In Selection
If Not R.HasFormula Then
a = R.Address
R.Value = Evaluate("Trim(" & a & ")")
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Fri, 22 Feb 2008 02:33:01 -0800, Gary''s Student
wrote:

There are two TRIM functions. In the worksheet TRIM removes leading and
trailing spaces. It also removes "extra" internal spaces.

In VBA, TRIM leaves the "internal" spaces alone. If you want to do the
automatic worksheet-style TRIM, then select the cells and run:

Sub dural()
For Each r In Selection
a = r.Address
r.Value = Evaluate("Trim(" & a & ")")
Next
End Sub

This will convert the values in-place