Trimming an entire Column of text

[CG]

Richard Scholler posted this solution on Ozgrid:

Sub TrimCol()
Dim r As Range
Set r = Intersect(ActiveCell.EntireColumn,
ActiveSheet.UsedRange)
r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address &
"<>"""",TRIM(" & r.Address & "),""""))")
End Sub

I just get a #NUM! when I use this. Does anyone know why? This
solution using Evaluate is in several places on the web.

http://www.ozgrid.com/forum/showthread.php?t=77535

 

[Héctor Miguel]

hi, !

Richard Scholler posted this solution on Ozgrid:

Sub TrimCol()
Dim r As Range
Set r = Intersect(ActiveCell.EntireColumn, ActiveSheet.UsedRange)
r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address & "<>"""",TRIM(" & r.Address & "),""""))")
End Sub

I just get a #NUM! when I use this. Does anyone know why?
This solution using Evaluate is in several places on the web...

try this way...

Sub TrimCol()
With Intersect(ActiveCell.EntireColumn, ActiveSheet.UsedRange)
.Value = Evaluate("transpose(transpose(trim(" & .Address & ")))")
End With
End Sub

hth,
hector.