Remove Space from Text String

[isleofman]

I have a text sting in column A which includes spaces. I want to remove the spaces and import the now reduced text string into column B.

How would I do that using VBA?

I don't want to use =SUBSTITUTE(A1," ","") as I wish to change the text string in column A and when I change the text sting the =SUBSTITUTE(A1," ","") is then required to be re-entered in column B every time.

I hope somebody understands what I'm saying here!
Many thanks.

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        .Offset(, 1).Value = Replace(.Value, " ", "")
    End With
Next i
End Sub

 

[isleofman]

Works a charm! Many thanks!

 

[Scott Huish]

Sub test()
Range("A:A").Copy Range("B:B")
Range("B:B").Replace " ", ""
End Sub