If you work with a lot of data in Excel, it is difficult to rearrange multiple column of data into a single column. The traditional way, you cut/copy and paste, and it will take forever (boring and repetitive works). The simple way, you able to used “macro”. Below are codes for macro in Excel for rearrange multiple columns into one and vice versa, respectively.

Multiple into one columns (Ozgrid.com, 2007):

Sub Multiple_Single() Dim k As Integer Dim R As Integer k = 2 R = 0 Columns("A:A").Insert Shift:=xlToRight Do Until R > 65536 Or Cells(65536, k).End(xlUp).Value = "" R = R + Range(Cells(1, k), Cells(65536, k).End(xlUp)).Rows.Count Range(Cells(1, k), Cells(65536, k).End(xlUp)).Copy Range("A65536").End(xlUp).Offset(1, 0) k = k + 1 Loop End Sub

One into multiple columns:

Sub Single_Multiple() Dim rng As Range Dim iCols As Integer Dim lRows As Long Dim iCol As Integer Dim lRow As Long Dim lRowSource As Long Dim x As Long Dim wks As Worksheet Set rng = Application.InputBox _ (prompt:="Select the range to convert", _ Type:=8) iCols = InputBox("How many columns do you want?") lRowSource = rng.Rows.Count lRows = lRowSource / iCols If lRows * iCols <> lRowSource Then lRows = lRows + 1 Set wks = Worksheets.Add lRow = 1 x = 1 For iCol = 1 To iCols Do While x <= lRows And lRow <= lRowSource Cells(x, iCol) = rng.Cells(lRow, 1) x = x + 1 lRow = lRow + 1 Loop x = 1 Next End Sub

References

- Ozgrid.com, (2007). Move Multiple Columns From Multiple Worksheets Into 1 Column. [online] Available at: http://www.ozgrid.com/forum/showthread.php?t=75449 [Accessed 17 Dec. 2014].