vba - 优化查找超过 200,000 个数据集的重复值

Question

我是编码新手，需要关于无法完成的代码的帮助。我怀疑这是由于数据集的大小。我使用简化的数据集测试了代码，它处理得很好。但是，我的实际数据集超过 210,000 行，并且预计会增长。
有没有办法加快这个速度？谢谢您的帮助

Sub DupValidation()
    Dim wb As Workbook
    Dim ws1 As Worksheet

    Dim i As Long
    Dim lastrow As Long
    Dim lastrow2 As Long

    Set wb = ActiveWorkbook
    Set ws1 = wb.Worksheets("Tickets")

    lastrow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

    ws1.Range("g2:g" & lastrow).ClearContents

    i = 2
    Do While i <= lastrow
        If Application.CountIf(ws1.Range(ws1.Cells(2, 2), ws1.Cells(lastrow, 2)), ws1.Cells(i, 2)) > 1 Then
            ws1.Cells(i, 7).Value = True
        End If
        i = i + 1
    Loop
End Sub

标签： vbaoptimizationduplicates

Answer 1

可能更好

Sub Check_Duplicates_Using_Evaluate()
    With Range("B2", Range("B" & Rows.Count).End(xlUp))
        .Offset(, 5).Value = .Parent.Evaluate("IF(COUNTIF(" & .Address & "," & .Address & ")>1,""True"","""")")
    End With
End Sub