Macro to Concatenate Columns in Excel 2010

[gard group=’1′]

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

[gard group=’3′]

Basic Macro Info

Basic Macro Info

Level of Difficulty


Works On

Excel 2007, 2010


This macro will allow a user to concatenate two columns.


Sub Concatenate_Column()
Do While ActiveCell <> ""
ActiveCell.Offset(0, 1).FormulaR1C1 = _
ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
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


If you need to get a deeper understanding of Excel Macros, we recommend the following courses at Udemy.

Excel With Excel Pivot Tables: GetPivotData & Macros

GetPivotData and Macros

This course teaches you in simple terms how to create a Macro to automate different Pivot Table commands. Additionally, you can learn how to use the GETPIVOTDATA formula in a Pivot Table to create custom reports that are not available with a Pivot Table. The course offers downloadable workbooks, two business case studies, and simple quizzes to test your understanding of the materials you studied. The course is available for $49.

Our second course recommendation is a rather simple one that teaches you the basics of macros and how to record macros. The details of the course are given below.

Microsoft Excel - Power Of Macros In 60 Minutes

Power of Macros

This course teaches you in simple terms the basics of macros & macro recording; elements of the macro recorder; recording simple & complex macros; optimizing a recorded code with some VBA lines and includes helpful examples for hands on training. The course is available for $19.

Related Post

Adeline Gear

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 playing games when she has the time.

Click Here to Leave a Comment Below 2 comments