Removing from a collection in Excel VBA

Question

In my VBA code, I am trying to generate a specific list of cell row positions.

Basically, I first fill a collection with the entire list of row positions pertaining to a specific column:

Dim arrPos As New Collection
....
For i = 3 To bottomRow
arrPos.Add i
Next i

Then I try to remove remove values from this collection if there's no problem at that specific row.

For h = matchRow To 3 Step -1
    For g = arrPos.Count To 1 Step -1
        If CLng(Worksheets(".....").Range("C" & h).Value) = arrPos(g) Then
            arrPos.Remove (g)
            Exit For
        End If
    Next g
Next h

Basically Range("C" & h).Value is a column where the =MATCH function was used so there's a whole list row positions in that column. If the MATCH worked, then I can remove it from the collection. A similar type of loop is made use of further down the code for the rows where the MATCH came up false.

The code gives the proper results but it can drag on at times (especially since row counts can get up to the 5000's) and even crash my puny laptop. As you can see, the method makes use of nested loops and I believe that significant results could be attained by re-factoring this portion.

Does anyone have any suggestions? The question is basically requesting a more efficient way to identify the row positions that did not come up from a MATCH function: either because the value was slightly erroneous or just simply missing.

Answer 1

The answer is to instead use VBA object Dictionary accessible by adding the "Microsoft Scripting Runtime" As a reference in your project. I don't know the internal mechanics but the dicitonary object has a method to see if an object .Exists within it's collection. This is much faster than my nested looping through an ordinary collection object and seeing if a specific value is contained.

Answer 2

Doesn't MATCH return NA when there's not match? If that's the way you have it set up and you want the rows where there are errors, then it seems like you could simplify it by just checking for errors and putting them into a 'no good' array.

Sub FilterMatch()

    Dim vaMatch As Variant
    Dim vaNoGood As Variant
    Dim lBottomRow As Long
    Dim i As Long, lCnt As Long

    lBottomRow = 32

    'Read in the range
    vaMatch = Range("c3").Resize(lBottomRow - 2).Value
    'Resize the array too big for now
    ReDim vaNoGood(1 To UBound(vaMatch, 1))

    For i = LBound(vaMatch, 1) To UBound(vaMatch, 1)
        'MATCH returns NA if no good, so only care about errors
        If IsError(vaMatch(i, 1)) Then
            lCnt = lCnt + 1
            'Save the row number of the error
            vaNoGood(lCnt) = i
        End If
    Next i

    'Reduce array based on actual results
    ReDim Preserve vaNoGood(1 To lCnt)

    'Print out the row numbers
    For i = LBound(vaNoGood) To UBound(vaNoGood)
        Debug.Print vaNoGood(i)
    Next i

End Sub