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.
- Tip: Tip: If you'd like to learn about how to create macros without writing them from scratch, check out our tutorial on Creating Macros Using the Macro Recorder.
Macro to Concatenate Columns in Excel 2010
Basic Macro Info
Basic Macro Info | |
|---|---|
Level of Difficulty | Easy |
Works On | Excel 2007, 2010 |
Description | This 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).
Ads by Google
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
COURSE RECOMMENDATIONS
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
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
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.
jefferis says
August 31, 2011 at 11:15 pmHi, 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 says
September 2, 2011 at 12:44 pmDid 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.