Home > Forum Home > Developing and Auditing Analytical Models > Concatenate two columns? > Concatenate two columns with VBA loop Share

Concatenate two columns with VBA loop

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


Forum TopicLogin

Concatenate Two Columns With Vba Loop

Rate this:
(2.5/5 from 2 votes)
HappyThe 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:
 ABCD
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 on
 
View Full Post

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