在Excel中找到区分大小写的副本(对于100 k或更多的记录)时，如何删除整个行？

Question

这是How to remove duplicates that are case SENSITIVE in Excel (for 100k records or more)? 的后续问题。

由于他的代码过程只操作A列的数据，如果找到case-sensitive复制，我也希望删除整个数据行。

区分大小写含义：

1. Case1
2. case1
3. cASE1

都是独一无二的记录。

Answer 1

您可以使用Dictionary来检查二进制唯一性和变体数组，以加快速度。要使用字典，需要包含对的引用

(工具>引用> Microsoft脚本运行库)

我在我的笔记本电脑上测试了100,000行，平均需要0.25秒。

Sub RemoveDuplicateRows()
    Dim data As Range
    Set data = ThisWorkbook.Worksheets("Sheet1").UsedRange

    Dim v As Variant, tags As Variant
    v = data
    ReDim tags(1 To UBound(v), 1 To 1)
    tags(1, 1) = 0 'keep the header

    Dim dict As Dictionary
    Set dict = New Dictionary
    dict.CompareMode = BinaryCompare

    Dim i As Long
    For i = LBound(v, 1) To UBound(v, 1)
        With dict
            If Not .Exists(v(i, 1)) Then 'v(i,1) comparing the values in the first column 
                tags(i, 1) = i
                .Add Key:=v(i, 1), Item:=vbNullString
            End If
        End With
    Next i

    Dim rngTags As Range
    Set rngTags = data.Columns(data.Columns.count + 1)
    rngTags.Value = tags

    Union(data, rngTags).Sort key1:=rngTags, Orientation:=xlTopToBottom, Header:=xlYes

    Dim count As Long
    count = rngTags.End(xlDown).Row

    rngTags.EntireColumn.Delete
    data.Resize(UBound(v, 1) - count + 1).Offset(count).EntireRow.Delete
End Sub

基于https://stackoverflow.com/questions/36873359/fastest-way-to-delete-rows-which-cannot-be-grabbed-with-specialcells的精彩答案