Excel – Combine multiple columns into one column

calendar_today Asked Jun 4, 2010
thumb_up 11 upvotes
history Updated April 16, 2026

Question posted 2010 · +5 upvotes

I have multiple lists that are in separate columns in excel. What I need to do is combine these columns of data into one big column. I do not care if there are duplicate entries, however I want it to skip row 1 of each column.

Also what about if ROW1 has headers from January to December, and the length of the columns are different and needs to be combine into one big column? 

ROW1| 1   2   3    
ROW2| A   D   G    
ROW3| B   E   H    
ROW4| C   F   I

should combine into 

A    
B    
C    
D    
E    
F    
G    
H    
I

The first row of each column needs to be skipped.

Accepted answer +11 upvotes

Try this. Click anywhere in your range of data and then use this macro:

Sub CombineColumns()
Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer

Set rng = ActiveCell.CurrentRegion
lastCell = rng.Columns(1).Rows.Count + 1

For iCol = 2 To rng.Columns.Count
    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

Excel VBA objects referenced (4)

  • Columns.Count — Count function (Microsoft Access SQL)
  • Columns.Count — Count the number of records in a DAO Recordset
  • Range — Refer to Cells by Using a Range Object
  • Range — Delete Duplicate Entries in a Range

Top excel-vba Q&A (6)

+11 upvotes ranks this answer #55 out of 136 excel-vba solutions on this site .
excel-vba