Populate multiple Variant arrays with loop and collection

[GeeWhiz7]

Hi folks,
I am trying to figure out how I can use a loop to
1) establish a number of arrays in a collection and 2) populate those individual arrays inside the loop.

Code I am trying to figure out below and a sample mini-sheet at the bottom.

Sub MakeAndFillArrays()
'#########
'Code should make multiple 2-D arrays inside a collection (in this case and
'then fill each of them with data from a table starting at the bottom and going up by 5 rows at a time
'each row of data gets put in a separate array in the collection
    
    Dim InSh, OutSh As Worksheet
    Dim lr, lc, i As Long
    
    Set InSh = Worksheets("Sheet1")
    Set OutSh = Worksheets("Sheet2")
    
    With InSh
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    End With
  
    OutSh.Cells.Clear
        
        Dim col As New Collection
        Dim a As Variant
        Dim n As Integer

            For n = 1 To 9
                a = Array()
                ReDim a(1 To n, 1 To lc)
                col.Add a
'             '####Now I would like to populate the arrays, possibly as they are being created one by one.
'             '####If I remove the line below from computation then the arrays all get set up, but I can't figure out how to populate them.
'             '####If I keep the line then it only ends up with one array in the collection with the last step (n=9) data.
               a = (.Range(.Cells(lr + 1 - 3, 1), .Cells(lr + 1 - 3, lc)).Value)  
            Next

End Sub

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Number	A	B	C	D	E
2	1	-44	-48	47	-2	14
3	2	-47	17	-45	3	-5
4	3	-31	19	-9	8	-13
5	4	-9	7	45	43	-33
6	5	-32	-39	-18	-13	1
7	6	-45	15	2	24	-9
8	7	-23	-25	29	14	1
9	8	32	6	-33	0	21
10	9	49	-33	-50	-37	-30
11	10	-22	12	-8	47	26
12	11	-18	-22	30	-2	-31
13	12	34	-29	-46	32	-3
14	13	32	38	-35	47	-4
15	14	-19	-15	50	-25	-35
16	15	20	14	9	-5	-45		Array(9)
17	16	-47	4	-27	-32	1
18	17	-35	-36	13	-23	18
19	18	-23	-23	-9	-4	1		Array(8)
20	19	28	-14	-17	6	43
21	20	-5	-29	11	46	-35
22	21	46	28	18	-20	-26		Array(7)
23	22	-42	21	-20	8	31
24	23	-44	-39	-14	38	15
25	24	9	-32	30	-48	-24		Array(6)
26	25	14	40	20	25	-4
27	26	22	-33	41	14	8
28	27	-7	-39	18	-22	21		Array(5)
29	28	-21	-5	17	-7	-32
30	29	2	22	-30	46	27
31	30	-13	-18	37	13	-16		Array(4)
32	31	28	-28	-36	10	3
33	32	-43	-4	20	-19	1
34	33	-3	-37	-8	50	31		Array(3)
35	34	-19	18	16	19	-14
36	35	-36	32	-46	-34	33
37	36	1	-16	28	-18	35		Array(2)
38	37	35	29	13	39	24
39	38	6	34	-23	-48	-30
40	40	-10	-44	33	40	10		Array(1)		Starts from bottom, step by 3, stops at 9 arrays
Sheet1

Cell Formulas
Range	Formula
A3:A39	A3	=A2+1

 

[Alex Blakenburg]

Does this do what you need ?

            For n = lr To 16 Step -3
                With InSh
                    a = (.Range(.Cells(n, 1), .Cells(n, lc)).Value)
                End With
                col.Add a
            Next

 

[GeeWhiz7]

Does this do what you need ?

            For n = lr To 16 Step -3
                With InSh
                    a = (.Range(.Cells(n, 1), .Cells(n, lc)).Value)
                End With
                col.Add a
            Next

Hi Alex, sorry for delay getting back. This still ends up with only one final array (at n = 16) instead of a(1), a(2), a(3) etc being separate. It correctly steps up from the bottom by -3 and the locals window shows each step has the correct values, but each new iteration is overwriting the previous array values.

 

[Alex Blakenburg]

You might need to clarify what you are trying to do.
Put the collection in the watch window and you should find 9 items each with 1 row in an array.
I have logged off for the night.

 

[GeeWhiz7]

You might need to clarify what you are trying to do.
Put the collection in the watch window and you should find 9 items each with 1 row in an array.
I have logged off for the night.

No worries and thanks for the help. I was able to get your suggestion to work. The main reason I was curious to use a collection instead of the outright array (and just count back rows there) was the ability to add/remove so this helps me with that.

 

[Alex Blakenburg]

Thanks for the follow up. I was going to suggest looking into using Dictionaries but they don't work on MacOS.
You don't see collections used that much but they can be useful when you don't know how many items you are going to add in advance.
It doesn't have the "exists" method that a dictionary has but there is a workaround for that.
Post another thread if you decide you want to pursue it further.