Trim(cll.value) will not remove "all spaces"...........only extra spaces.


Gord

On Wed, 14 Oct 2009 18:48:29 +0100, p45cal
wrote:


camlad;525281 Wrote:
I need to remove white space from a range so I recorded the following
which
did not work.



How can I remove all spaces in a range?



Range("A1:A535").Select

Selection.Replace What:="^w", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False



Camlad

See if this works for you:

Sub blah()
For Each cll In Range("A1:A535").Cells
cll.Value = Application.WorksheetFunction.Trim(cll.Value)
Next cll
End Sub