Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Copying multiple columns into one column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2004
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copying multiple columns into one column

    Hello,

    I need to copy multiple columns (around 400) into one column. does anyone have a macro or know of a way to do this quickly
    Column1 Column2 Column3 Column4
    320000000016001 320001000016001 320002000016001 320004000016001
    320000000016002 320001000016002 320002000016002 320004000016002
    320000000016005 320001000016005 320002000016005 320004000016005
    320000000016010 320001000016010 320002000016010 320004000016010
    320000000016012 320001000016012 320002000016012 320004000016012
    320000000016015 320001000016015 320002000016015 320004000016015
    320000000016104 320001000016104 320002000016104 320004000016104


    Column1

    320000000016001
    320000000016002
    320000000016005
    320000000016010
    320000000016012
    320000000016015
    320000000016104
    320001000016001
    320001000016002
    320001000016005
    320001000016010
    320001000016012
    320001000016015
    320001000016104
    320002000016001
    320002000016002
    320002000016005
    320002000016010
    320002000016012
    320002000016015
    320002000016104
    320004000016001
    320004000016002
    320004000016005
    320004000016010
    320004000016012
    320004000016015
    320004000016104

    thanks

  2. #2
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying multiple columns into one column

    Hi,

    Do the columns all have headers in row 1? Are they all the same number of rows? Do you want the new single column on a new sheet? Or on the same sheet with the old columns deleted?

  3. #3
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying multiple columns into one column

    As a simplified example, with your data like this:

    Sheet1
    B C
    1 1 6
    2 2 7
    3 3 8
    4 4 9
    5 5 10
    Excel 2010

    Running this code:
    Code:
    Sub example()
    
    Dim vIn     As Variant
    Dim vOut    As Variant
    Dim i       As Long
    Dim j       As Long
    Dim ub1     As Long
    Dim ub2     As Long
    
    
    vIn = Application.Transpose(Range("B1").CurrentRegion)
    ub1 = UBound(vIn, 1)
    ub2 = UBound(vIn, 2)
    ReDim vOut(1 To ub1 * ub2, 1 To 1)
    For i = 1 To ub1
        For j = 1 To ub2
            vOut(j + (ub2 * (i - 1)), 1) = vIn(i, j)
        Next j
    Next i
    Range("A1:A" & (ub1 * ub2)) = vOut
    
    
    End Sub
    Would give you this result:

    Sheet1
    A B C
    1 1 1 6
    2 2 2 7
    3 3 3 8
    4 4 4 9
    5 5 5 10
    6 6
    7 7
    8 8
    9 9
    10 10
    Excel 2010

    Obviously you will need to tweak it for your particular situation but that is an example pattern you could try.

  4. #4
    New Member
    Join Date
    May 2004
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying multiple columns into one column

    Yes, they all have the same number of rows and yes they have a column header for each column. It doesn't matter if the new column is in a new sheet or the same sheet

  5. #5
    New Member
    Join Date
    Aug 2012
    Location
    Melbourne, Australia
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying multiple columns into one column

    Sorry to hijack the thread, looks like OP's questions has been answered.
    I made one a few weeks ago that did the opposite.
    It took data from one long column and split it across columns A-I and i think 45 rows down to fill one page. It was very basic, i recorded the first copy/paste as a macro and then used that code and adjusted the references for all the other columns. It ended up as a really long repetitive code but it works. Anyone able to give an easier way to do it?
    Small sample of the code
    Code:
    Sheets("Sheet 1").Select
        Range("A2:A47").Select
        Selection.Copy
        Sheets("Sheet 2").Select
        Range("A2").Select
        ActiveSheet.Paste
        Sheets("Sheet 1").Select
        ActiveWindow.SmallScroll Down:=14
        Range("A48:A93").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet 2").Select
        Range("B2").Select
        ActiveSheet.Paste
    Last edited by Tatts; Aug 15th, 2012 at 06:42 PM.

  6. #6
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying multiple columns into one column

    Quote Originally Posted by Canes70 View Post
    Yes, they all have the same number of rows and yes they have a column header for each column. It doesn't matter if the new column is in a new sheet or the same sheet
    Ok, then perhaps try this - the result is output to column A on Sheet2:

    Code:
    Sub example()
    
    Dim vIn     As Variant
    Dim vOut    As Variant
    Dim i       As Long
    Dim j       As Long
    Dim ub1     As Long
    Dim ub2     As Long
    
    With Sheets("Sheet1").Range("A1").CurrentRegion
        vIn = Application.Transpose(.Offset(1, 0).Resize(.Rows.Count - 1))
    End With
    ub1 = UBound(vIn, 1)
    ub2 = UBound(vIn, 2)
    ReDim vOut(1 To ub1 * ub2, 1 To 1)
    For i = 1 To ub1
        For j = 1 To ub2
            vOut(j + (ub2 * (i - 1)), 1) = vIn(i, j)
        Next j
    Next i
    Sheets("Sheet2").Range("A1:A" & (ub1 * ub2)) = vOut
    
    End Sub
    Sample data before running the code on Sheet1:

    Sheet1
    A B C
    1 Col1 Col2 Col3
    2 1 5 9
    3 2 6 10
    4 3 7 11
    5 4 8 12
    Excel 2010

    Sample output on Sheet2 after running the code:

    Sheet2
    A
    1 1
    2 2
    3 3
    4 4
    5 5
    6 6
    7 7
    8 8
    9 9
    10 10
    11 11
    12 12
    Excel 2010
    Last edited by circledchicken; Aug 15th, 2012 at 06:46 PM. Reason: Changed sample data to Sheet1

  7. #7
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying multiple columns into one column

    Quote Originally Posted by Tatts View Post
    Sorry to hijack the thread, looks like OP's questions has been answered.
    I made one a few weeks ago that did the opposite.
    It took data from one long column and split it across columns A-I and i think 45 rows down to fill one page. It was very basic, i recorded the first copy/paste as a macro and then used that code and adjusted the references for all the other columns. It ended up as a really long repetitive code but it works. Anyone able to give an easier way to do it?
    Small sample of the code
    Hi,

    Perhaps try this reverse procedure:

    Code:
    Sub example()
    
    Dim vIn     As Variant
    Dim vOut()  As Variant
    Dim i       As Long
    Dim j       As Long
    Dim ub1     As Long
    Dim ub2     As Long
    
    vIn = Application.Transpose(Sheets("Sheet1").Range("A1").CurrentRegion)
    ub1 = 9 ' max number of columns
    ub2 = Application.RoundUp(UBound(vIn, 1) / ub1, 0)
    ReDim vOut(1 To ub2, 1 To ub1)
    For i = 1 To ub1
        For j = 1 To ub2
            On Error Resume Next
            vOut(j, i) = vIn(j + (ub2 * (i - 1)))
            On Error GoTo 0
        Next j
    Next i
    Sheets("Sheet2").Range("A1").Resize(ub2, ub1) = vOut
    
    End Sub
    This assumes:


    • Your data starts in A1 of Sheet1 (with no header)
    • The output is placed on Sheet2

  8. #8
    New Member
    Join Date
    May 2004
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying multiple columns into one column

    Thanks circledchicken. that wroked

  9. #9
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying multiple columns into one column

    Quote Originally Posted by Canes70 View Post
    Thanks circledchicken. that wroked
    Your welcome.

  10. #10
    New Member
    Join Date
    Aug 2012
    Location
    Melbourne, Australia
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying multiple columns into one column

    It works so long as i only have 450 lines in column A. If there are any more then the data will go onto the second page eg column A will have 1-50 then B will have 51-100.
    After splitting it will be converted to pdf. I'm happy for it to go to a second page but want it to start on page 2 in column A50 (451) from the number after I50 (450).
    I only know some very basic vba and i can't figure out how this works and not sure how to edit to make it do what i would like.
    If you have time i'd be interested in a bit of an explanation on how your code works.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •