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.
| Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
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
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
