Maybe you can pick up all the values in 3 two dimensional arrays (1000 rows x 1
column) and then just combine them in VBA. I bet most of the delay is caused by
going back to the worksheet to get those 1000 * 3 values.

(Heck, you may not even have to make that 3 column array--just use each of the
single column arrays???)

Option Explicit
Sub testme()

Dim Col1Array As Variant
Dim Col2Array As Variant
Dim Col3Array As Variant
Dim NewArray() As Variant
Dim HowManyRows As Long
Dim iCtr As Long

HowManyRows = 1000

Col1Array = Worksheets("All_Data").Range("F6").Resize(HowManyR ows, 1)
Col2Array = Worksheets("All_Data").Range("l6").Resize(HowManyR ows, 1)
Col3Array = Worksheets("All_Data").Range("v6").Resize(HowManyR ows, 1)

ReDim NewArray(1 To HowManyRows, 0 To 2)
For iCtr = LBound(Col1Array, 1) To UBound(Col1Array, 1)
NewArray(iCtr, 0) = Col1Array(iCtr, 1)
NewArray(iCtr, 1) = Col2Array(iCtr, 1)
NewArray(iCtr, 2) = Col3Array(iCtr, 1)
Next iCtr

End Sub

======
You may want to experiment by getting a single array (1000 rows x 17 columns)
and seeing if that impacts the speed of your macro. Just use the columns you
want and ignore the 14 you don't need.


Raul wrote:

I need to know if a multi-dimensional array can be populated directly from
non-contiguous ranges in a worksheet?

I can do the following but it gets pretty slow when the number of rows
exceeds 20000.

For i = 1 to 10000
NewArray(i,0) = Worksheets("All_Data").Cells(5 + i, 6)).Value
NewArray(i,1) = Worksheets("All_Data").Cells(5 + i, 12)).Value
NewArray(i,2) = Worksheets("All_Data").Cells(5 + i, 22)).Value
Next i

I can also populate one-dimensional arrays directly from a range and then
use a For Next Loop to populate NewArray(1 to 10000, 0 to 2)

DateArray =
Application.Transpose(ThisWorkbook.Sheets("All_Dat a").Range("F6:F10000"))
DataArray1 =
Application.Transpose(ThisWorkbook.Sheets("All_Dat a").Range("L6:L10000"))
DataArray2 =
Application.Transpose(ThisWorkbook.Sheets("All_Dat a").Range("V6:V10000"))

For i = 1 to 10000
NewArray(i,0) = DateArray(i)
NewArray(i,1) = DataArray1(i)
NewArray(i,2) = DataArray2(i)
Next i

But Id like to be able to use something on the order of:

Application.Index(NewArray, 0, 1) =
Application.Transpose(ThisWorkbook.Sheets("All_Dat a").Range("L6:L1000"))

Or

Application.Index(NewArray, 0, 1) = DataArray1

Is this possible? If it is, can you give me an example?

Thanks in advance,
Raul

--

Dave Peterson