We have been working on an Excel project for some time now and when creating a database we encountered a problem. We had to merge the contents of two cells and doing this manually was time consuming due to the huge volume of records. We decided to put our VBA skills to the test and came out with this neat little macro to concatenate columns which I hope will be of help to you. Don’t forget to check out the other macros we have on our site.
Macro to Concatenate Columns in Excel 2010
Basic Macro Info
|Level of Difficulty||Easy|
|Description||This macro will allow a user to concatenate two columns.|
Do While ActiveCell <> “”
ActiveCell.Offset(0, 1).FormulaR1C1 = _
ActiveCell.Offset(0, -1) & ” ” & ActiveCell.Offset(0, 0)
How to Use the Macro
1. Copy the code.
2. Open Excel 2010.
3. Press Alt + F11 to enter the Visual Basic Editor.
4. If necessary, press Ctrl + R to show the Project Explorer.
5. Right-click desired file on left (in bold).
6. Choose Insert -> Module.
7. Paste code into the right pane.
8. Press Alt + Q to close the VBE.
9. Save the workbook in the XLSM format.
10. Select the Developer tab. (If you have a problem displaying the Developer tab, please read our post How to Activate the Developer Tab in Excel 2010.
11. On the Developer tab, in the Code group, click Macros.
12. In the Macro dialog box, select the macro and click Run.
Sample Workbook with Macro
To test the macro, please download the sample file Excel VBA Concatenate.xlsm