how to assign ranges on different sheets to an array

[KRCowen]

I need to load 4 columns of data from two different worksheets into a list box
on a userform. I can load all the data (the data columns will all be the same,
and they will always have 4000 to 10000 rows) into an array and assign the
array to be the rowsource for the listbox. Currently, I am doing this by
reading each cell value in a loop through each column. Is there a more
efficient way to simply assign the ranges to the array without looping through
all the data?

Thanks

Ken

 

[Tom Ogilvy]

Use an intermediate array. Pick up the column of values (or two adjacent
columns of values if adjacent) all at once, then loop through that making
the assignments to the array.
Dim var as Variant
varr = Range("A1:B2000").Value

now var is a (1 x 2000, 1 x 2) array.

Regards,
Tom Ogilvy

 

[Ryan Poth]

You should be able to achieve the same result by simply
assigning the "Value" property to the list, as in the
following example:

MyForm.MyListBox.List = Range("A1:A5000").Value

 

[Tom Ogilvy]

Unfortunately, you can't union ranges on separate sheets.

Even if they are on the same sheet:

Sub Tester1()
varr = Union(Range("Sheet1!A1:A5000"), Range("Sheet1!C1:C5000"), _
Range("Sheet1!e1:e5000"), Range("Sheet1!F1:F5000")).Value
Debug.Print UBound(varr, 1), UBound(varr, 2)
End Sub

You only get the first area in the varr.

My previous advice was sound.

Regards,
Tom Ogilvy