Combinning Multiple Lists

[TK84916]

I am trying to combine three different lists into one without bein
limited to the space between the number of the lists. For example:

Fruit Veggie Meat
Orange Carrot Beef
Pear Eggplant Chicken
Apple Broccoli Veal
Lemon Cabbage
Lime

I want the three lists to become one like this:

Orange
Pear
Apple
Lemon
Lime
Carrot
Eggplant
Broccoli
Cabbage
Beef
Chicken
Veal

Having quite a hard time on doing this. The real hard part is that th
lists have variable lengths. And would perfer to not have a limit o
the length of the lists.

Thank You in advance

T

 

[Gord Dibben]

I gleaned this code from the News Group or from a website.

Apologies to originator for not attributing.

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