combine multiple columns in excel into one column [Excel VBA]

If you want to combine multiple columns in excel into one column in excel using excel VBA macro, then use below code.

This macro combines data in multiple columns into one column.

• When you run the macro, it asks you to enter the number of columns to combine (starting from column A), For example, If you are thinking on “how to merge two columns in excel”, enter 2 and press ENTER button

VBA macro to combine multiple columns in excel into one column

Sub combineMultipleColumnsData()
' This macro combines data in multiple columns into a single column
Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer
Dim numOfColumns As Integer
Set rng = ActiveCell.CurrentRegion
iCol = 1
numOfColumns = InputBox("Enter Number of Columns to Combine, Starting from Column A")
Range("A1").Activate
Cells.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
lastCell = rng.Columns(iCol).Rows.Count + 1
For iCol = 2 To numOfColumns
Application.Columns(iCol).SortSpecial SortMethod:=xlPinYin
    Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
    ActiveSheet.Paste Destination:=Cells(lastCell, 1)
    lastCell = lastCell + rng.Columns(iCol).Rows.Count
Next iCol
End Sub

Where to paste VBA code in Excel?

1. Copy above code
2. Open Microsoft excel
3. Press ALT+F11 (keyboard shortcut to open VBA editor)
4. Paste the code in VBA editor (at the beginning)
5. Click on save button
6. Close the VBA editor window.

How to run the macro in excel?

1. Press ALT+F8 (keyboard shortcut to view macros).below window opens
2. Select ‘combineMultipleColumnsData’ macro
3. Click ‘Run’ button

Also see VBA macro to remove empty columns