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

• mango: I wantt the missing positions too. As in even if match didn't produce n/a, the positions not returned as well