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