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

TRIM remove only the space defined by CHAR(32)
There is also the 'no-breaking' space, CHAR(160)
Have a look at this site; lots of suggestions
http://office.microsoft.com/en-us/ex...561311033.aspx

and Ron has good stuff at
http://www.rondebruin.nl/clean.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ayo" wrote in message
...
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

"Ayo" wrote:

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

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

"Mike H" wrote:

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

"Ayo" wrote:

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

Good point

"Bernard Liengme" wrote:

TRIM remove only the space defined by CHAR(32)
There is also the 'no-breaking' space, CHAR(160)
Have a look at this site; lots of suggestions
http://office.microsoft.com/en-us/ex...561311033.aspx

and Ron has good stuff at
http://www.rondebruin.nl/clean.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ayo" wrote in message
...
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

How do I write CHAR(160) in VBA code?

"Bernard Liengme" wrote:

TRIM remove only the space defined by CHAR(32)
There is also the 'no-breaking' space, CHAR(160)
Have a look at this site; lots of suggestions
http://office.microsoft.com/en-us/ex...561311033.aspx

and Ron has good stuff at
http://www.rondebruin.nl/clean.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ayo" wrote in message
...
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

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


Gord Dibben MS Excel MVP

On Fri, 18 Jul 2008 12:07:03 -0700, Ayo wrote:

How do I write CHAR(160) in VBA code?

"Bernard Liengme" wrote:

TRIM remove only the space defined by CHAR(32)
There is also the 'no-breaking' space, CHAR(160)
Have a look at this site; lots of suggestions
http://office.microsoft.com/en-us/ex...561311033.aspx

and Ron has good stuff at
http://www.rondebruin.nl/clean.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ayo" wrote in message
...
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

Thanks Gord. Works perfectly.

"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP

On Fri, 18 Jul 2008 12:07:03 -0700, Ayo wrote:

How do I write CHAR(160) in VBA code?

"Bernard Liengme" wrote:

TRIM remove only the space defined by CHAR(32)
There is also the 'no-breaking' space, CHAR(160)
Have a look at this site; lots of suggestions
http://office.microsoft.com/en-us/ex...561311033.aspx

and Ron has good stuff at
http://www.rondebruin.nl/clean.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ayo" wrote in message
...
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