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-


--
noelcantona
------------------------------------------------------------------------
noelcantona's Profile: http://www.excelforum.com/member.php...o&userid=28147
View this thread: http://www.excelforum.com/showthread...hreadid=476609

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
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-