How to combine multiple columns into one list in Excel?

To combine or merge multiple columns into one long list, normally, you can copy and paste the columns data one by one into the specified column. But, here, I can introduce you some quick tricks to solve this task in Excel.

Combine multiple columns into one list with VBA code

Combine multiple columns into one list with Kutools for Excel

Combine multiple columns into one list with VBA code

Without repeating to copy and paste the columns data, the following VBA code may help you to deal with this job quickly and easily.

1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

VBA code: Combine or merge multiple columns into one list

Sub CombineColumns1()
'updateby Extendoffice
    Dim xRng As Range
    Dim i As Integer
    Dim xLastRow As Integer
    Dim xTxt As String
    On Error Resume Next
    xTxt = Application.ActiveWindow.RangeSelection.Address
    Set xRng = Application.InputBox("please select the data range", "Kutools for Excel", xTxt, , , , , 8)
    If xRng Is Nothing Then Exit Sub
    xLastRow = xRng.Columns(1).Rows.Count + 1
    For i = 2 To xRng.Columns.Count
        Range(xRng.Cells(1, i), xRng.Cells(xRng.Columns(i).Rows.Count, i)).Cut
        ActiveSheet.Paste Destination:=xRng.Cells(xLastRow, 1)
        xLastRow = xLastRow + xRng.Columns(i).Rows.Count
    Next
End Sub

3. Then press F5 key to run this code, and a prompt box will pop out to remind you select the data range that you want to combine into only one column, see screenshot:

4. And then click OK button, and the selected columns have been merged into only one column as following screenshots shown:

Tips: After running this VBA, the original data of the range will be cleared, you’d better copy and save them in another location first.

Combine multiple columns into one list with Kutools for Excel

If you have installed Kutools for Excel, its Transform Range utility can help you combine multiple columns into one single column or single row.

After installing Kutools for Excel, please do as follows:

1. Select the columns data that you want to combine into one list.

2. Then click Kutools > Range > Transform Range, see screenshot:

3. In the Transform Range dialog box, select Range to single column option, and click Ok button, see screenshot:

4. In the following popped out Transform Range box, click a cell where you want to output the result, see screenshot:

5. Then click OK button, your selected columns have been combined into one single column in the specified location you desired. See screenshot:

Download and free trial Kutools for Excel Now !

Demo: Combine multiple columns into one list with Kutools for Excel