Fastest way to find a row in excel range using Exc

Question

I have got an excel spreadsheet (sheet2) with records of count around 1 Million. I am iterating these records and for each iteration I am comparing a row of selected columns against another range of around 2000 records which is in sheet1.

rangeA = 1 Million rows 'Sheet2
rangeB = 2000 rows 'Sheet1

With sheet1
For Each row In rangeA.Columns.Rows

   For Each rangeBRow In rangeB.Columns.Rows
     If (.Cells(rangeBRow.Row,1).Value = CName And .Cells(rangeBRow.Row,2).Value = LBL ... ) Then
     ' Do something cool... set some cell value in sheet2
     Exit For
     End If
   Next rangeBRow

Next row
End With

My problem with the above code is that it is taking forever to complete the execution. Is there some other fastest and a quick way to find a row against a range of rows in excel macro other than iterating a million records for 2000 rows?

Thank you for your time.

Answer 1

12 seconds to check 5k rows against 200k:

Sub Compare()

    Dim rngA As Range, rngB As Range
    Dim dict As Object, rw As Range
    Dim a As Application, tmp As String

    Set a = Application
    Set dict = CreateObject("scripting.dictionary")

    Set rngA = Sheet1.Range("A2:F200000")
    Set rngB = Sheet1.Range("K2:P5000")

    For Each rw In rngA.Rows
        'Create a key from the entire row and map to row
        ' If your rows are not unique you'll have to do a bit more work here
        ' and use an array for the key value(s)
        dict.Add Join(a.Transpose(a.Transpose(rw.Value)), Chr(0)), rw.Row
    Next rw

    For Each rw In rngB.Rows
        'does this row match one in range A?
        tmp = Join(a.Transpose(a.Transpose(rw.Value)), Chr(0))
        If dict.exists(tmp) Then
            rw.Cells(1).Offset(0, -1).Value = dict(tmp)
        End If
    Next rw

End Sub