Combining mutiple columns into one column

Greetings to all,

I have this problem combining mutiple columns to one column only.

For e.g.

Column A= 10 rows
Column B= 10 rows
Column C= 13 rows

I would like to have only 1 column with data of Column A, B and C (total of 33 rows in this case).

I have a daily of 300 columns coming in, so I need a good long term solution.

Thank you.

cheers,
-NoeL-

NoeL

I believe this macro was posted by Dave Peterson or Bob Phillips.

Whomever was originator, it works.

Sub OneColumn()

''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
''''''''''''''''''''''''''''''''''''''''''

Dim ilastcol As Long
Dim ilastrow As Long
Dim jlastrow As Long
Dim colndx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim idx As Integer

Set ws = ActiveWorkbook.ActiveSheet
ilastcol = Cells(1, Columns.Count).End(xlToLeft).Column

With Sheets.Add
.Name = "Alldata"
End With

idx = Sheets("Alldata").Index
Sheets(idx + 1).Activate

For colndx = 1 To ilastcol

ilastrow = ws.Cells(Rows.Count, colndx).End(xlUp).Row
jlastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row

Set myRng = Range(Cells(1, colndx), _
Cells(ilastrow, colndx))
With myRng
.Copy Sheets("Alldata").Cells(jlastrow + 1, 1)
End With
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

End Sub


Gord Dibben Excel MVP


On Sun, 16 Oct 2005 10:38:20 -0500, noelcantona
<noelcantona.1x05qa_1129478710.689@excelforum-nospam.com> wrote:

>
>Greetings to all,
>
>I have this problem combining mutiple columns to one column only.
>
>For e.g.
>
>Column A= 10 rows
>Column B= 10 rows
>Column C= 13 rows
>
>I would like to have only 1 column with data of Column A, B and C
>(total of 33 rows in this case).
>
>I have a daily of 300 columns coming in, so I need a good long term
>solution.
>
>Thank you.
>
>cheers,
>-NoeL-