Speed up Loop VBA

[Rick Rothstein]

Hi,

Performed a test with the macro Sub kTest.
I get the following results: 0.110 seconds

Remarkably faster, in line with the timing of a sort "sort" the same range of three columns

I liked Admin's use of the Dictionary to cut down on the number of loops from the method I used. Just wondering if using a Collection might yield good results (possibly not because of the need to convert your unique numbers to text in order to use them as Keys for the Collection). Can you test this out to see what kind of execution time it yields for the database you ran the other code against?

Sub MarkPositionNumbers3()
    
    Dim Data As Variant, UnqList As Variant, Pos() As Variant
    Dim X As Long, Coll As New Collection
    
    UnqList = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Data = Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row)
    ReDim Pos(1 To UBound(Data, 1), 1 To 1)
    
    For X = 1 To UBound(UnqList)
        Coll.Add X, CStr(UnqList(X, 1))
    Next
    
    On Error Resume Next
    For X = 1 To UBound(Data)
        If Len(Data(X, 1)) Then Pos(X, 1) = Coll(CStr(Data(X, 1)))
    Next
    
    Range("H2").Resize(UBound(Pos, 1)) = Pos
    
End Sub