The App Times

Macro to Concatenate Columns in Excel 2010

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.

Get the latest update in your inbox.

Subscribe to our mailing list and get tips, tutorials, and app reviews in your email inbox.

Adeline is a tech enthusiast who loves exploring the latest tools and applications in the marketplace. She also loves her Android and spends time reviewing apps and games on android-resources.net.

View all contributions by

    Comments on this entry are closed.

    • jefferis August 31, 2011, 11:15 pm

      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)

      • Eddie Gear September 2, 2011, 12:44 pm

        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.

    Get the latest updates
    in your inbox

    Subscribe to our mailing list and get tips, tutorials, and app reviews in your email inbox.