Concatenate Two Columns With Vba Loop: Concatenate two columns? Excel help response (20 Apr 2006)

Excel Help for Concatenate Two Columns With Vba Loop in Developing and Auditing Analytical Models

Forum Topic

Concatenate Two Columns With Vba Loop

Rate this:

(2.5/5 from 2 votes)

The easiest and most flexible way to concatenate two columns of text into a list of all possible combinations is to do it via a VBA macro.

If, for example, you have the following spreadsheet:
Excel Spreadsheet:

	A	B	C	D
1	Alabama	Hotel
2	Alaska	Theatre
3	Arizona
4	Texas
5	Washington

You could then run the following macro to concatenate each possible combination of city and site in column D:
VBA Code:

Sub combine_columns()
Dim town As Variant
Dim site As Variant
Range("D1").Select 'Change as appropriate
For Each site In Range("B1:B2") 'Change as appropriate
    For Each town In Range("A1:A5") 'Change as appropriate
        ActiveCell.Formula = town & " " & site
        ActiveCell.Offset(1, 0).Select
    Next
Next
Range("D1").Select
End Sub

Try opening your VBA editor from Tools >> Macro >> Visual Basic Editor, insert a new module by choosing Insert >> Module and paste the above code in. The macro will now be available from Tools >> Macro >> Macros.

Excel Business Forums Administrator

Posted by Excel Helper on 20 Apr 2006

View Full Post

Find relevant Excel templates and add-ins for Concatenate two columns with VBA loop in the Excel Business Solutions Directory

Concatenate two columns with VBA loop

Excel Help for Concatenate Two Columns With Vba Loop in Developing and Auditing Analytical Models

Concatenate Two Columns With Vba Loop