Using Collection instead of Array

[tiredofit]

This code removes all rows if column E contains Oranges

    Dim DataArray() As Variant
    
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
    
    Dim DataArrayRows As Long
    
    DataArrayRows = UBound(DataArray(), 1)
    
    Dim DataArrayCols As Long
    
    DataArrayCols = UBound(DataArray(), 2)
    
    Dim SubArray() As Variant
    
    ReDim SubArray(1 To DataArrayRows, 1 To DataArrayCols) As Variant
    
    Dim I As Long, j As Long, k As Long
    
    j = 1
    
    k = 1
    
    For i = 2 To DataArrayRows
    
        If DataArray(i, 5) <> "Oranges" Then
        
            For j = 1 To DataArrayCols
        
                SubArray(k, j) = DataArray(i, j)
            
            Next j
            
            k = k + 1
            
        End If
    
    Next I
    
    Sheet3.Cells(2, 1).Resize(k - 1, DataArrayCols).Value = SubArray()

This line is an overkill because I am declaring a SubArray that has as many rows as DataArray.

ReDim SubArray(1 To DataArrayRows, 1 To DataArrayCols) As Variant

Can I use a Collection instead? If so, how can I specify the second dimension?

If my data has 10 columns, I don't want to have to create 10 collections, eg:

Dim DataArray() As Variant
    
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
    
    Dim DataArrayRows As Long
    
    DataArrayRows = UBound(DataArray(), 1)
    
    Dim DataArrayCols As Long
    
    DataArrayCols = UBound(DataArray(), 2)
    
    Dim SubArray() As Variant
    
    ReDim SubArray(1 To DataArrayRows, 1 To DataArrayCols) As Variant
    
    Dim I As Long, j As Long, k As Long
    
    j = 1
    
    k = 1
    
    Dim Coll1 As Collection
    Dim Coll2 As Collection
    Dim Coll3 As Collection
    Dim Coll4 As Collection
    Dim Coll5 As Collection
    Dim Coll6 As Collection
    Dim Coll7 As Collection
    Dim Coll8 As Collection
    Dim Coll9 As Collection
    Dim Coll10 As Collection
    Set Coll1 = New Collection
    Set Coll2 = New Collection
    Set Coll3 = New Collection
    Set Coll4 = New Collection
    Set Coll5 = New Collection
    Set Coll6 = New Collection
    Set Coll7 = New Collection
    Set Coll8 = New Collection
    Set Coll9 = New Collection
    
    Set Coll10 = New Collection
    For i = 2 To DataArrayCols
    
        If DataArray(i, 12) <> "RD" Then
        
            Coll1.Add DataArray(i, 1)
            Coll2.Add DataArray(i, 2)
            Coll3.Add DataArray(i, 3)
            Coll4.Add DataArray(i, 4)
            Coll5.Add DataArray(i, 5)
            Coll6.Add DataArray(i, 6)
            Coll7.Add DataArray(i, 7)
            Coll8.Add DataArray(i, 8)
            Coll9.Add DataArray(i, 9)
            Coll10.Add DataArray(i, 10)
        
            k = k + 1
            
        End If
    
    Next i
    
    Sheet3.Cells(2, 1).Resize(k - 1, DataArrayCols).Value = SubArray()

Thanks

 

[Norie]

Why is this overkill?

ReDim SubArray(1 To DataArrayRows, 1 To DataArrayCols) As Variant

Isn't it possible both arrays could have the same no of rows (minus the header perhaps)?

They definitely appear to need to have the same no of columns.

 

[tiredofit]

Why is this overkill?

ReDim SubArray(1 To DataArrayRows, 1 To DataArrayCols) As Variant

Isn't it possible both arrays could have the same no of rows (minus the header perhaps)?

They definitely appear to need to have the same no of columns.

The chances are, SubArray is going to have far fewer rows than DataArray.

I thought using a collection where you "add on need to" basis would be preferable.

 

[Fluff]

I'd stick with the array. IMO it's simpler

 

[tiredofit]

Thanks