Trimming an entire Column of text

  • Thread starter Thread starter CG
  • Start date Start date
C

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
 
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...
Click to expand...

try this way...

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

hth,
hector.
 
Back
Top