excel - 循环的大型电子表格比较。有更快的方法吗？

Question

我目前正在使用 For 循环遍历 10k 行电子表格，并将数据与另一个类似大小的电子表格进行比较。正如您可能已经猜到的那样，这需要相当长的时间。有更快的方法吗？

我尝试使用从一张纸的顶部开始的 For 循环，然后使用 Find 方法将每一行的信息与第二张纸进行比较。我无法使用自动筛选方法以任何方式缩短搜索范围。

Private Sub Import_NewReport()

Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set wb1 = ThisWorkbook
Set wb2 = Application.Workbooks.Open("C:\Users\Owner\Desktop\Order Entry 
Summary - Detail Report.csv")
Set ws1 = wb1.Worksheets("Master")
Set ws2 = wb2.Worksheets("Order Entry Summary - Detail Re")
ws1LastRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
ws2LastRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
ws2.AutoFilterMode = False
i = 1  

For ws2Row = 2 To ws2LastRow

JN = ws2.Cells(ws2Row, "D").Text ' Job Number from New Report

If ws1.Range("D:D").Find(JN) Is Nothing Then

ws1.Cells(ws1LastRow + i, "A").EntireRow.Value = ws2.Cells(ws2Row, "A").EntireRow.Value
ws1.Cells(ws1.Range("D:D").Find(JN).Row, "BB").Value = GetGUID

i = i + 1

Else

rTo = ws1.Range("D:D").Find(JN).Row
ws2.Cells(ws2Row, "A").Resize(, 10).Value = ws1.Cells(rTo, "A").Resize(, 10).Value
ws2.Cells(ws2Row, "M").Resize(, 2).Value = ws1.Cells(rTo, "M").Resize(, 2).Value
ws2.Cells(ws2Row, "Z").Value = ws1.Cells(rTo, "Z").Value
ws2.Cells(ws2Row, "AH").Value = ws1.Cells(rTo, "AH").Value
ws2.Cells(ws2Row, "AB").Value = ws1.Cells(rTo, "AB").Value
ws2.Cells(ws2Row, "AX").Value = ws1.Cells(rTo, "AX").Value

If ws1.Cells(rTo, "BB").Value = "" Then
ws1.Cells(rTo, "BB").Value = GetGUID
End If
End If
Next ws2Row
End Sub

标签： excelvba