Trim question

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
 
M

Mike H

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
 
A

Ayo

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...
 
G

Gord Dibben

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


Gord Dibben MS Excel MVP
 
A

Ayo

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with ActiveCell.EntireRow.Delete 8
Template location changes from user to user 0
AddPicturesToShapes 0
VBA Coding Help for Beginner 0
Extract Text in braquet and list of terms from 2 Docs 3
"Unable to get the VLookup property of the WorksheetFunction class 4
Is it possible to reset the starting cell in a For Loop 2
type mismatch error in macro in excel 0

Top