在 Excel 中发现区分大小写的重复项时(对于 100k 记录或更多)，如何删除整行? [英] How to delete entire row when case sensitive duplicates are found in Excel (for 100k records or more)?

问题描述

这是 如何删除 Excel 中区分大小写的重复项(对于 100k 记录或更多)?.

由于他的代码过程仅操作 A 列的数据，如果发现区分大小写重复，我还想删除整行数据.

Since his code procedure manipulates the data of column A only, I'd like to also delete the entire row of data if case-sensitive duplicate is found.

区分大小写的含义:

1. 案例 1
2. 案例1
3. 案例1

都是唯一的记录.

推荐答案

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

You can use a Dictionary to check for binary uniqueness and variant arrays to speed things up. To use the dictionary you will need to include a reference to Microsoft Scripting Runtime Library

(工具 > 参考 > Microsoft 脚本运行库)

(Tools > References > Microsoft Scripting Runtime library)

我已经用 100,000 行对此进行了测试，在我的笔记本电脑上平均需要 0.25 秒.

I've tested this with 100,000 rows which takes on average 0.25 seconds on my laptop.

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

基于 这个问题

这篇关于在 Excel 中发现区分大小写的重复项时(对于 100k 记录或更多)，如何删除整行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！