# Removing from a collection in Excel VBA

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
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.

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.

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

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

• I wantt the missing positions too. As in even if match didn't produce n/a, the positions not returned as well – mango Mar 22 '13 at 1:06