使用Excel宏VBA在Excel范围内找到一行最快的方法 [英] Fastest way to find a row in excel range using Excel Macro VBA

问题描述

我有一个excel电子表格（sheet2），记录约100万。我正在迭代这些记录，对于每次迭代，我比较了一列选定的列与另一个范围大约2000个记录在sheet1中。

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

我上面的代码的问题是它永远是完成执行。有没有其他一些最快捷的方式可以找到一行排列在excel宏的一系列行，而不是迭代一千万条记录2000行？

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?

谢谢你的时间。

推荐答案

12秒钟检查5k行与200k：

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

这篇关于使用Excel宏VBA在Excel范围内找到一行最快的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！