in that case I'd take a different route..
if you do not know the size you need beforehand
i'd dispense with the arrays and use a dictionary
object to collect the data.
set a reference to Microsoft Scripting Runtime
Sub DumpIn1Column()
Dim d As New Scripting.Dictionary
Dim r As Variant, c As Range, i As Long
For Each r In Array(Range("b9:l9"), Range("b14:l14"))
For Each c In r.Cells
If Len(c) Then
i = i + 1
d.Add i, c.Value
End If
Next
Next
Range("z1").Resize(UBound(d.Items) + 1, _
1) = Application.Transpose(d.Items)
End Sub
note:
application.Transpose allows for 5761 items in all versions
more (unlimited) in xl2002 and xl2003, but looking at your question
it should be more than enough.
no more *extra* questions.. please!
--
keepITcool
|
www.XLsupport.com | keepITcool chello nl | amsterdam
wrote :
Extra Question:
I then want to paste all Array data into one long column in the
worksheet.
Can this be done by pasting an array column by the next array column
or not.
Again I know i can do it with nested FOR statements easily but want to
understand alternative methods there are of inputting and retreiving
sets of data from Arrays.