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.