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?
- Copy above code
- Open Microsoft excel
- Press ALT+F11 (keyboard shortcut to open VBA editor)
- Paste the code in VBA editor (at the beginning)
- Click on save button
- Close the VBA editor window.
How to run the macro in excel?
- Press ALT+F8 (keyboard shortcut to view macros).below window opens
- Select ‘combineMultipleColumnsData’ macro
- Click ‘Run’ button
Also see VBA macro to remove empty columns