Big Array from several worksheets

[Albert]

Hello!
I have a very large array which has to be filled with the information from
several worksheets.
Thus far, the strategy I use is to Create an independent Array for the info
on each Worksheet, and then I Consolidate the arrays into one (see procedure
below). However, this is a time consuming procedure which takes up to one
minute.
Maybe someone has some advice to do this faster?
I was thinking something in the lines of...

Sub ArrayConsolidator()
Dim ArrayUnion(1 To 40, 1 To 2) As Variant
ArrayUnion(1 to 20,1 to 2)=Range("A1","B20")
ArrayUnion(21 to 40,1 to 2)=Range("A21","B40")
End Sub

This of course, does not work, but the idea is to fill the big array
partially, and thus not having to consolidate it later. Filling the array by
"Brute Force" is not an option, since the sheets have 65,000 X 12 entries
each, and it takes for ever!
Following is the procedure I currently use to consolidate the arrays.
Best regards,
Albert C.

Sub CallArrayConsolidator
call CallArrayConsolidator(Array1, Array2, Array3)
End sub
Sub ArrayConsolidator(ParamArray vArr())

Dim i As Long, j As Long, k As Long
Dim vE As Variant

i = 0
For Each vE In vArr
i = i + UBound(vE, 1)
Next vE

ReDim MatrizMovimientos(1 To i, 1 To TotalFields)

i = 0
For Each vE In vArr
For j = 1 To UBound(vE, 1)
i = i + 1
For k = 1 To TotalFields
MatrizMovimientos(i, k) = vE(j, k)
Next k
Next j
Next vE

End Sub

 

[Bob Phillips]

Why not just use

Dim ArrayUnion

ArrayUnion = Application.Transpose(Range("A1:B40"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Albert]

Hey Bob.
I would, but in reality, the ranges are in different sheets.