Make small variant array from big array based on Value

[GeeWhiz7]

Hi folks,
I have a variant array that I would would like to break up into individual arrays and I'm realizing I haven't figured out how to do that yet.
Mini-sheet example below.

Goal: In vba (not in sheet) array would like to either
A) Make smaller arrays for each department based on the department name from a bigger array
or
B) perhaps preferably, make arrays directly based on the department name and avoid the bigger array step

Mini-sheet

Book4
	A	B	C
1	Dept	Wk 1	Wk 2
2	Manufacturing	17	154
3	Manufacturing	186	16
4	Manufacturing	33	114
5	Manufacturing	85	71
6	Manufacturing	112	120
7	Manufacturing	130	23
8	Manufacturing	185	114
9	Manufacturing	24	86
10	Manufacturing	139	107
11	Manufacturing	83	49
12	Manufacturing	39	33
13	Sales	99	146
14	Sales	112	87
15	Sales	185	195
16	Sales	186	135
17	Sales	129	86
18	Sales	54	90
19	Sales	52	21
20	Sales	188	187
21	Sales	188	23
22	Marketing	159	192
23	Marketing	141	177
24	Marketing	70	80
25	Marketing	80	72
26	Marketing	20	79
27	HR	35	85
28	HR	108	94
29	HR	122	128
30	HR	29	132
Sheet1

My thinking up to a point...but then I realized I'm not sure what the best way to do this would.

Sub Test()
Dim a, aManu, aSales, aMkt, aHR, aAll As Variant
Dim lr, lc As Long

Dim iSh As Worksheet
   
Set iSh = Worksheets("Sheet1")
   
    With iSh
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
       
        aAll = .Range(.Cells(2, 2), .Cells(lr, lc)).Value
       
            '### For Loops here to get individual arrays??
            '### how to size the small array before knowing how many department entries?
            '### aManu, aSales, aMkt, aHR
       
    End With
End Sub

 

[JGordon11]

Sub Test()

    Dim addrAll As String, addrDept As String, Depts As Variant
    Dim lr, lc As Long, i As Long, ubDepts As Long
    
    Dim iSh As Worksheet
       
    Set iSh = Worksheets("Sheet1")
    
    Depts = Array("", "Manufacturing", "Sales", "Marketing", "HR")
    ubDepts = UBound(Depts)
    ReDim a(1 To ubDepts) As Variant
   
    With iSh
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        addrAll = .Range(.Cells(2, 2), .Cells(lr, lc)).Address
        addrDept = .Range(.Cells(2, 1), .Cells(lr, 1)).Address
    End With
    
    For i = 1 To ubDepts
        a(i) = Evaluate("=Filter(" & addrAll & ", " & addrDept & "= """ & Depts(i) & """)")
    Next
    ' a(1) <=> aManu, a(2) <=> aSales, a(3) <=> aMkt, a(4) <=> aHR

End Sub

 

[GeeWhiz7]

Sub Test()

    Dim addrAll As String, addrDept As String, Depts As Variant
    Dim lr, lc As Long, i As Long, ubDepts As Long
   
    Dim iSh As Worksheet
      
    Set iSh = Worksheets("Sheet1")
   
    Depts = Array("", "Manufacturing", "Sales", "Marketing", "HR")
    ubDepts = UBound(Depts)
    ReDim a(1 To ubDepts) As Variant
  
    With iSh
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        addrAll = .Range(.Cells(2, 2), .Cells(lr, lc)).Address
        addrDept = .Range(.Cells(2, 1), .Cells(lr, 1)).Address
    End With
   
    For i = 1 To ubDepts
        a(i) = Evaluate("=Filter(" & addrAll & ", " & addrDept & "= """ & Depts(i) & """)")
    Next
    ' a(1) <=> aManu, a(2) <=> aSales, a(3) <=> aMkt, a(4) <=> aHR

End Sub

Thank you JGordon11 this works nicely.
I'm not that knowledgeable on Evaluate, but reading on the internet, I think it makes sense the way that you've constructed the code.