Copying multiple columns into one column

Canes70

New Member
Joined
May 27, 2004
Messages
8
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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?
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
As a simplified example, with your data like this:

Sheet1
BC
116
227
338
449
5510

<thead>
</thead><tbody>
</tbody>
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
ABC
1116
2227
3338
4449
55510
66
77
88
99
1010

<thead>
</thead><tbody>
</tbody>
Excel 2010

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

Canes70

New Member
Joined
May 27, 2004
Messages
8
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
 

Tatts

New Member
Joined
Aug 8, 2012
Messages
46
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:

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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
ABC
1Col1Col2Col3
2159
32610
43711
54812

<tbody>
</tbody>
Excel 2010

Sample output on Sheet2 after running the code:

Sheet2
A
11
22
33
44
55
66
77
88
99
1010
1111
1212

<tbody>
</tbody>
Excel 2010
 
Last edited:

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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
 

Tatts

New Member
Joined
Aug 8, 2012
Messages
46
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,551
Messages
5,487,522
Members
407,604
Latest member
sama9000

This Week's Hot Topics

Top