Populate a multi-dimensional array from Ranges

[Raul]

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_Data").Range("F6:F10000"))
DataArray1 =
Application.Transpose(ThisWorkbook.Sheets("All_Data").Range("L6:L10000"))
DataArray2 =
Application.Transpose(ThisWorkbook.Sheets("All_Data").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_Data").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

 

[Peter T]

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]

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

 

[Dave Peterson]

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(HowManyRows, 1)
Col2Array = Worksheets("All_Data").Range("l6").Resize(HowManyRows, 1)
Col3Array = Worksheets("All_Data").Range("v6").Resize(HowManyRows, 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.

 

[Peter T]

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]

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]

You will be working with a 1d array of 2d arrays.

' add to bottom of sub abc()
' after - MsgBox multiArr(3)(10, 1) ' E10

Dim k As Long, r As Long, c As Long, r as Long

i = 0
For j = 1 To UBound(multiArr)
For r = 1 To UBound(multiArr(j))
i = i + 1
Cells(i, 10) = j & " " & r & " " & multiArr(j)(r, 1)

Next
Next

All your arrays are one column wide. If more than one or unknown you might
need a third inner loop, eg

For j = 1 To UBound(multiArr)
For r = 1 To UBound(multiArr(j))
For c = 1 To UBound(multiArr(j), 2)
i = i + 1
Cells(i, 10) = j & " " & r & " " & multiArr(j)(r, c)
Next
Next
Next

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

That's fine, but only FWIW could have done (not tested, potential typo !)

Set Destrng = Range("G1:I10")

With Destrng
For i = 1 to ubound(multiArr)
..columns(i).value = multiArr(i)
next
end with

Regards,
Peter T

 

[Raul]

This helps a lot Peter!

Thank you very much for the explanations and examples.

Raul