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