Y yhoy Feb 22, 2008 #1 Similar to the TRIM() function but I would like to find a more automated way.. Thanks!
G Gary''s Student Feb 22, 2008 #2 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
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
G Gord Dibben Feb 22, 2008 #3 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
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
