Incrementally Slicing 1D Worksheet Range Into 2D Array in VBA

[dazedandconfused123]

Hello,

Fairly new to VBA and feel like this is out of my league.

I want to slice a one column range in my excel worksheet every time the type changes (it's uniform so it will always be i.e. 3 iterations of a type) into a 2D array in VBA.

I need it sliced precisely because I will be indexing the array and using it's values to populate other arrays based whether or not it meets certain conditions.

Example Data in Worksheet:

Type:
1
1
1
2
2
2
3
3
3
4
4
4

Desired Outcome in VBA Array:
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4

 

[Eric W]

Welcome to the MrExcel forum!

How are you planning on using the output array? As an input to a worksheet formula, or in another VBA routine? In your example you have 3 instances of each type, but your desired outcome has 4 instances of each type? Where is the list of types located? Column A perhaps?

 

[dazedandconfused123]

Welcome to the MrExcel forum!

How are you planning on using the output array? As an input to a worksheet formula, or in another VBA routine? In your example you have 3 instances of each type, but your desired outcome has 4 instances of each type? Where is the list of types located? Column A perhaps?

My apologies. The desired array is supposed to be the below based off the previous data:

1 1 1
2 2 2
3 3 3
4 4 4

It will be used for another VBA routine. I plan on iterating through each element in the new 2D array and storing the value in a second array if if the value meets certain criteria using if statements.

I just need help slicing the range imported from the Worksheet. Thanks!

 

[Eric W]

Assuming your list starts in cell A2, and that there is nothing after your list, try:

Sub test1()
Dim MyArray()
Dim MyData As Variant
Dim NumCols As Long
Dim i As Long, j As Long, r As Long

    NumCols = 3
    MyData = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    ReDim MyArray(1 To UBound(MyData) / NumCols, 1 To NumCols)
    r = 1
    For i = 1 To UBound(MyData) / NumCols
        For j = 1 To NumCols
            MyArray(i, j) = MyData(r, 1)
            r = r + 1
        Next j
    Next i
   
End Sub

There are actually shorter ways to do this, but I thought this would be pretty clear how it works. Depending on how you want to filter this array, you could use your criteria here and kill 2 birds with one stone.