Trim question

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I want to use the following code to remove space at the end of the value in
each cell in a coulmn but the space is still there. Any ideas what is going
on?
Thanks.

Private Sub cmdRemoveSpace_Click()
Dim ClastRow As Integer, c As Range, cl As Range
ClastRow = ActiveSheet.UsedRange.Rows.Count

For Each c In Range("A2:A" & ClastRow).Cells
'c = Mid(c, 1, Len(c) - 1)
c = Trim(c)
Next c

End Sub
 
Maybe

Private Sub cmdRemoveSpace_Click()
Dim ClastRow As Integer, c As Range, cl As Range
ClastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set cl = Range("A2:A" & ClastRow)
For Each c In cl
c = WorksheetFunction.Trim(c)
Next c

End Sub

Mike
 
That didn't work. The space is still there.

Mike H said:
Maybe

Private Sub cmdRemoveSpace_Click()
Dim ClastRow As Integer, c As Range, cl As Range
ClastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set cl = Range("A2:A" & ClastRow)
For Each c In cl
c = WorksheetFunction.Trim(c)
Next c

End Sub

Mike
Click to expand...
 
For Each c In Range("A2:A" & ClastRow).Cells
c.Replace what:=Chr(160), replacement:=""
Next c


Gord Dibben MS Excel MVP
 
Thanks Gord. Works perfectly.

Gord Dibben said:
For Each c In Range("A2:A" & ClastRow).Cells
c.Replace what:=Chr(160), replacement:=""
Next c


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top