Collections instead of arrays

[tiredofit]

I have some data that consists of 10 columns.

I want to choose only those rows where columnd 10 is "Apples"

I can define an array consisting of a fixed number (10 in this case) columns and a variable number of rows, such as:

Dim OutputArray() As Variant
ReDim Preserve OutputArray(1 to Counter, 1 to 10) As Variant

However, I would like to do this using collections instead.

My problem is how can I define a collection to input 10 columns of data?

Thanks

 

[RoryA]

You can add arrays to a collection:

    Dim c As Collection
    Dim n As Long
    
    Set c = New Collection
    
    c.Add Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10), "item1"
    
    For n = LBound(c("item1")) To UBound(c("item1"))
        MsgBox c("item1")(n)
    Next n

BTW, your sample code wouldn't actually work since you can only resize the last dimension of an array if you use Preserve.

 

[tiredofit]

Yes thanks for spotting my mistake but it was done quickly to illustrate the point.

In any case, I generally do not use ReDim Preserve, as I read somewhere it is "expensive".

Instead, I perfer to do two loops, one to find the dimension I want, then use ReDim.