loop function to remove spaces.

Hi,
I have a code to remove spaces but as there are up to 4 spaces before text, I want to loop the code but not sure how to do it.

I tried
Sub spacer()

For Each r In ActiveSheet.UsedRange
Do While Left(r.Value, 1) = " "
If Left(r.Value, 1) = " " Then r.Value = Right(r.Value, Len(r.Value) - 1)
Next r
Loop

End Sub

Thanks

I tried that but when I went to copy the trimmed data across as values, it wouldn't paste as some of the cells had merged cells. Can you apply a trim to a column in situ?

Like TMS mentioned, this should work.

Please Login or Register  to view this content.

Like TMS and jolivanes suggested, the trim function should work here. Another efficient way is to loop through the constant text values directly and use the trim$ function for strings:

Please Login or Register  to view this content.

I thought this worked but when I applied it, it seems to get stuck and keeps on running. I have to crash out of it.

I thought this worked

Which code is this?

This one
Sub spacer()
Application.ScreenUpdating = False
Dim r As Range
For Each r In ActiveSheet.UsedRange
r.Value = Trim(r.Value)
Next r
Application.ScreenUpdating = True
End Sub

Tusen Takk

If your sheet has formatted cells well past what you think is the UsedRange, you could try this.

Please Login or Register  to view this content.

BTW, using UsedRange can be very deceiving.