Combine Multiple Columns into One on Excel

September 2016


The use of this macro will enable you to transpose, or convert, data from multiple rows and columns on a Microsoft Excel spreadsheet into a single column.

Convert Rows & Columns into Single Column

Let's say you have the following data in an Excel spreadsheet:
480,000 600,000 720,000  
520,000 650,000 780,000
300,000 375,000 450,000
432,000 540,000 648,000
260,000 325,000 390,000
304,000 380,000 456,000
340,000 425,000 510,000
304,000 380,000 456,000


Our macro will allow you to convert the above data so that it reads like this on your document:
480,000  
520,000
300,000
432,000
260,000
304,000
340,000
304,000

600,000
650,000
375,000
540,000
325,000
380,000
425,000
380,000

720,000
780,000
450,000
648,000
390,000
456,000
510,000
456,000

This macro is programmed with an assumption that your data ranges from row 1 to row 8 and columns A to C. Note that you may tweak the macro to match your exact data set.

Also note that macro test is an operative macro, while undo will undo the macro's result.
Sub test() 
Dim j As Long, k As Long, r As Range, dest As Range
j = Range("A1").End(xlToRight).Column
For k = 1 To j
Set r = Range(Cells(1, k), Cells(1, k).End(xlDown))
r.Copy
Set dest = Cells(Rows.Count, "A").End(xlUp).Offset(3, 0)
dest.PasteSpecial
Next k
End Sub



Sub undo()
Dim r As Range
Set r = Range("a1").End(xlDown).Offset(1, 0)
Set r = Range(r, Cells(Rows.Count, "A").End(xlUp))
r.EntireRow.Delete

End Sub

Related :

This document entitled « Combine Multiple Columns into One on Excel » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.