Macro to Concatenate Columns in Excel 2010

4 Flares 4 Flares ×

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 DifficultyEasy
Works On2007,2010
DescriptionThis macro will allow a user to concatenate two columns.

Code:

Sub Concatenate_Column()
Do While ActiveCell <> “”
ActiveCell.Offset(0, 1).FormulaR1C1 = _
ActiveCell.Offset(0, -1) & ” ” & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

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

Follow us on Twitter, Facebook or Google +to get the latest tutorials, updates and how-to articles on Microsoft, Google and Apple products.

Comments

  1. says

    Hi, I’m trying this macro in Excel 2011 and am getting this error:
    Compile error expected =
    Or if I separate sub concatenate I get a syntax error
    Sub Concatenate_Column() <— arrow points here

    ActiveCell.Offset(0, 1).FormulaR1C1 = _ //these two lines highlighted in red
    ActiveCell.Offset(0, -1) & ” ” & ActiveCell.Offset(0, 0)

    • says

      Did you try the code as given in the post without making any changes. Because It works fine as is. But if you have made changes, could you provide us with your complete code so we can try and help you out.