Thanks Peter,
I added the following to code to output the values of multiArr to G1:I10.

Set Destrng = Range("G1:G10,H1:H10,I1:I10")
For i = 1 To rng.Areas.Count
Destrng.Areas(i).Value = multiArr(i)
Next

Now that I know that multiArr contains the values it is supposed to, what
syntax is required to loop through each row and column of multiArr to perform
some function?

Thanks,
Raul



"Peter T" wrote:

On a new sheet try the following

Sub abc()
Dim i As Long
Dim arr
Dim rng As Range, cell As Range
Dim rArea As Range

Set rng = Range("A1:C10")
For Each cell In rng
cell.Value = cell.Address(0, 0)
Next
arr = rng.Value

MsgBox arr(UBound(arr), UBound(arr, 2)) ' C10


Set rng = Range("A1:A10,C1:C10, E1:E10")
For Each rArea In rng.Areas
If rArea.Rows.Count maxRows Then maxRows = rArea.Rows.Count

For Each cell In rArea
cell.Value = cell.Address(0, 0)
Next
Next

ReDim multiArr(1 To rng.Areas.Count)
For i = 1 To rng.Areas.Count
multiArr(i) = rng.Areas(i).Value
Next

MsgBox multiArr(3)(10, 1) ' E10


End Sub

Regards,
Peter T



"Raul" wrote in message
...
Peter,
Thank you very much for your response.
I want to end up with an array called newArray(1 to numRows, 0 to 3)

With
newArray(1,0) = worksheets("All_data").Cells(6,6).value "value in F6".
newArray(1,1) = worksheets("All_data").Cells(6,12).value "value in L6"
etc.

Suggestion 1 does not work for me.

Dim arr As Variant
Dim MyRange As Range

Set MyRange = Range(Worksheets("All_Data").Cells(6, 6), _
Worksheets("All_Data").Cells(1000, 6))

arr = myRange 'does not work for me
however,
arr = application.transpose(myRange) 'works but I don't know why.

Suggestion 2 is also giving me trouble

Dim multiArr(1 to 3) 'As Variant ?
multiArr(I) = .Range("F6:F10000").Value

How would I loop through this to verify the data?

Suggestion 3 looks like it should work, but it is not working either.

Thanks,
Raul


"Peter T" wrote:

I don't follow all of what you are trying to do.

You can assign values in a single area range to an array in one go like
this -

Dim arr as Variant
arr = myRange

arr() will now be a two-D array populated with all the values in the
range,
and sized like this (even if myRange is only one cell)

(1 to myRange.Rows.Count, 1 to myRange.Columns.Count)

If you want to populate a single 3D array with values from a multiple
area
range -

Dim multiArr(1 to 3)

multiArr(I) = .Range("F6:F10000").Value
'etc
x = multiArr(1,1,1) ' value in F6


Alternatively, to end up with a single 2D array try something like this
(noting in your example all 3 ranges are single columns)

cnt = rng(1).rows.count + rng2.rows.count + rng3.rows.count
Set bigRng = union(rng1, rng2, rng3)
ReDim bigArray(1 to cnt, 1 to 1) ' < change the 1 to 1 as necessary

n = 0
For Each rArea in bigRng
arr = rArea.Value

For i = 1 to UBound(arr)
n = n + 1
bigArray(n,1) = arr(i)
Next
Next


Regards,
Peter T




"Raul" wrote in message
...
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 I'd 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