Loops

Hi Thea,

I have written some code (see below) based on your question . If you copy and paste this code into the code window of your spreadsheet's VBE, you could test it out on some sample data. It worked on my sample spreadsheet. I tried to emulate your spreadsheet's layout.

Sub JoinAddresses()

Columns("AC:AC").Select

Selection.Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove
'This line adds a column and moves any existing content to the next column.

Range("AC1").Select
ActiveCell = "Address" 'You can replace "Address" with any text
Range("AC2").Select

Do Until ActiveCell.Offset(0, -28) = "" 'Looks at the data in column A

ActiveCell = "=(RC[-3]&"" ""&RC[-2]&"" ""&RC[-1])" 'Concatenates 3 columns
ActiveCell.Offset(1, 0).Select

Loop

Columns("AC:AC").AutoFit

End Sub


Kind regards
Marius Barnard
Excel Trainer