Multiple into one columns and vice versa in Excel using macro

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

  1. 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].
This entry was posted in Code and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *