寻求高效Excel宏：解决大行数下重复行检测卡顿崩溃问题

你的问题很典型——嵌套循环在小数据量下没问题，但数据量上去后（比如7000+行），O(n²)的时间复杂度会让Excel直接卡顿甚至崩溃。原代码的核心问题有两个：一是嵌套循环导致重复计算量爆炸，二是频繁直接操作单元格对象，这在VBA里是非常耗时的操作。

下面给你几个高效的实现方案，从VBA优化到内置功能都有：

方案一：使用字典（Dictionary）+ 数组批量处理（推荐） #

这个方案把时间复杂度降到O(n)，同时通过数组减少和工作表的交互，是VBA处理大数据量的标准优化思路：

Sub MarkDuplicateRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range
    Dim dataArr As Variant
    Dim dict As Object
    Dim i As Long
    Dim rowKey As String
    
    ' 指定目标工作表，可替换为你的表名如Sheet1
    Set ws = ActiveSheet
    ' 获取数据区域的最后一行（1-19列）
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set dataRange = ws.Range("A1:S" & lastRow) ' S是第19列，对应你的需求
    dataArr = dataRange.Value ' 一次性读取所有数据到数组
    
    ' 初始化字典，用于存储每行的唯一标识
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 关闭Excel的后台操作提升速度
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    ' 遍历每一行，检测重复
    For i = 1 To lastRow
        ' 将当前行的19列内容拼接成唯一字符串作为字典的键
        ' 用特殊分隔符（比如|）避免不同内容拼接后冲突
        rowKey = Join(Application.Index(dataArr, i, 0), "|")
        
        If dict.Exists(rowKey) Then
            ' 标记重复行的1-19列为红色，批量操作比循环列更快
            dataRange.Rows(i).Interior.ColorIndex = 3
        Else
            ' 首次出现的行，加入字典记录
            dict.Add rowKey, i
        End If
    Next i
    
    ' 恢复Excel的正常功能
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
    ' 释放对象内存
    Set dict = Nothing
    Set dataRange = Nothing
    Set ws = Nothing
End Sub

为什么这个方案更快？ #

• 数组读取：一次性把所有数据读到内存数组里，避免了循环中反复读写单元格（单元格操作是VBA中最慢的操作之一）。
• 字典查找：字典的键值对查找是O(1)时间，只需要遍历一次所有行，7000行就是7000次循环，而原代码是7000*7000=4900万次循环，效率差了几个数量级。
• 批量格式设置：直接对整行区域设置颜色，比逐个单元格循环快很多。

方案二：利用Excel内置的条件格式（无需VBA） #

如果你不需要用宏，Excel的条件格式功能其实可以高效实现这个需求，而且性能更好（因为是Excel原生优化）：

1. 选中你要检测的区域（A1:S7000，假设最后一行是7000）。
2. 点击「开始」→「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」。
3. 输入公式：

   =COUNTIFS($A$1:$A$7000,$A1,$B$1:$B$7000,$B1,$C$1:$C$7000,$C1,$D$1:$D$7000,$D1,$E$1:$E$7000,$E1,$F$1:$F$7000,$F1,$G$1:$G$7000,$G1,$H$1:$H$7000,$H1,$I$1:$I$7000,$I1,$J$1:$J$7000,$J1,$K$1:$K$7000,$K1,$L$1:$L$7000,$L1,$M$1:$M$7000,$M1,$N$1:$N$7000,$N1,$O$1:$O$7000,$O1,$P$1:$P$7000,$P1,$Q$1:$Q$7000,$Q1,$R$1:$R$7000,$R1,$S$1:$S$7000,$S1)>1
   

4. 设置格式为填充红色，点击确定。

这个方法的好处是Excel会自动处理，不需要运行宏，而且数据更新时格式会自动刷新。

原代码的问题补充说明 #

顺便提一下原代码里的几个小问题：

• 变量y和z没有初始化，第一次运行时会默认从0开始，导致Cells(y,1)出错。
• 嵌套循环中每次都遍历整个A列，大量重复比较，比如第1行和第2行比较后，第2行又会和第1行重复比较一次。
• 直接操作Cells对象，循环中反复读写单元格，性能极低。

内容的提问来源于stack exchange，提问作者Cell