VBA脚本比较2张纸并删除重复的单元格“大搜索范围" [英] VBA script to compare 2 sheets and delete duplicated cells “Big Search Range”

问题描述

我想要一个脚本，将工作表1单元格"与工作表2单元格"进行比较，并从工作表2中删除重复项.我现在正在使用此脚本

I want a script that compare "sheet 1 cells" with "sheet 2 Cells" and delete duplicates from sheet 2. I am using now this script

Option Explicit
Sub CleanDupes()
Dim targetArray, searchArray
Dim targetRange As Range
Dim x As Long

'Update these 4 lines if your target and search ranges change
Dim TargetSheetName As String: TargetSheetName = "Sheet2"
Dim TargetSheetColumn As String: TargetSheetColumn = "A"
Dim SearchSheetName As String: SearchSheetName = "Sheet1"
Dim SearchSheetColumn As String: SearchSheetColumn = "A"

'Load target array
With Sheets(TargetSheetName)
    Set targetRange = .Range(.Range(TargetSheetColumn & "1"), _
            .Range(TargetSheetColumn & Rows.Count).End(xlUp))
    targetArray = targetRange
End With
'Load Search Array
With Sheets(SearchSheetName)
    searchArray = .Range(.Range(SearchSheetColumn & "1"), _
            .Range(SearchSheetColumn & Rows.Count).End(xlUp))
End With


Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = 0
If IsArray(searchArray) Then
    For x = 1 To UBound(searchArray)
        If Not dict.exists(searchArray(x, 1)) Then
            dict.Add searchArray(x, 1), 1
        End If
    Next
Else
    If Not dict.exists(searchArray) Then
        dict.Add searchArray, 1
    End If
End If

'Delete rows with values found in dictionary
If IsArray(targetArray) Then
    'Step backwards to avoid deleting the wrong rows.
    For x = UBound(targetArray) To 1 Step -1
        If dict.exists(targetArray(x, 1)) Then
            targetRange.Cells(x).EntireRow.Delete
        End If
    Next
Else
    If dict.exists(targetArray) Then
        targetRange.EntireRow.Delete
    End If
End If
End Sub

但是我面临搜索范围的问题，Excel将工作表行限制为一百万左右，每个月我的搜索范围都会增加30万行，所以我很快就无法使用此脚本.我要求任何解决此限制的方法.

But I am facing problem with my search range, Excel limits the sheet rows by around 1 million, every month my search range will be increased by 300k row, so soon I can't use this script. I am asking for any method to play around this limitation.

注意:-我的目标范围将放置在工作表2中的A列中，并且不会超出限制.-脚本应区分大小写.(ccse3E，cCse3e不是重复的)

Notes: -- My target range will be placed in sheet 2, column A and will not exceed the limitation. -- The script should be case sensitive. (ccse3E, cCse3e aren't duplicates)

推荐答案

如果您需要超过100万行，我是否建议您考虑使用数据库解决方案.Excel只是无法缩放.

If you need more than 1 million rows, can I suggest that you consider moving to a database solution. Excel just won't scale.

这篇关于VBA脚本比较2张纸并删除重复的单元格“大搜索范围"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！