|
Populate a multi-dimensional array from Ranges
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 |
Populate a multi-dimensional array from Ranges
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 |
Populate a multi-dimensional array from Ranges
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 dont 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 |
Populate a multi-dimensional array from Ranges
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 |
Populate a multi-dimensional array from Ranges
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 |
Populate a multi-dimensional array from Ranges
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 |
Populate a multi-dimensional array from Ranges
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" wrote in message ... 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 |
Populate a multi-dimensional array from Ranges
This helps a lot Peter!
Thank you very much for the explanations and examples. Raul "Peter T" wrote: 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 08:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com