无需辅助列隐藏重复单元格：能否用数组作为AutoFilter筛选条件？

问题背景 #

需要隐藏指定区域内的重复单元格，使用AdvancedFilter虽能实现，但会导致后续无法进行普通筛选。目前使用依赖辅助列的代码可实现需求，希望在不使用辅助列的前提下达成同样效果，询问是否可将唯一单元格的地址存入数组，再作为AutoFilter的筛选条件？隐藏重复后需保留手动进行普通筛选的能力。

原依赖辅助列的代码：

Sub Hide_visible_duplicate_cells_(procRng As Range)
    Dim arng As Range, C As Range, dict As New Scripting.Dictionary
    Dim arrMark, colMark As Range, lastC As Long, sh As Worksheet, lastR As Long, i As Long
    
    Const markName As String = "Marker_column"
    
    Set arng = procRng.SpecialCells(xlCellTypeVisible)
    
    If arng Is Nothing Then MsgBox "Not a valid Range": Exit Sub
    
    Set sh = procRng.Parent 'the sheet where the range belongs to

    lastR = sh.UsedRange.rows(sh.UsedRange.rows.count).row  'last row OF THE SHEET
    ReDim arrMark(1 To lastR, 1 To 1) 'redim the markers array
    
    'determinte the column where the marker to be placed (or it already exists):
    Set colMark = sh.rows(procRng.cells(1).row).Find(What:=markName, LookIn:=xlValues, LookAt:=xlWhole)
    If Not colMark Is Nothing Then
        lastC = colMark.column  'for the case when the marker column exists
    Else
        lastC = sh.cells(procRng.cells(1).row, sh.Columns.count).End(xlToLeft).column + 1 'next empty column if marker column does not exist
        'to correct the last column number, IF LAST COLUMN IS HIDDEN (it MUST HAVE A HEADER):
        If sh.cells(procRng.cells(1).row, lastC).Value <> "" Then lastC = lastC + 1
    End If
    
    For Each C In arng.cells
        If Not dict.Exists(C.Value) Then
            If i > 0 Then                                            'to skip the first cell, which should be on the headers row
                dict.Add C.Value, vbNullString       'Keep the first occurrence
                arrMark(C.row - procRng.cells(1).row, 1) = "True"      'place the marker for the first occurrence
            End If
            If C.Value <> "" Then i = i + 1 'for the case of empty cells above the header...
        End If
    Next C
    'place the marker column header, if not already existing:
     If colMark Is Nothing Then sh.cells(procRng.cells(1).row, lastC).Value = markName 'place the marker column name, IF NOT EXISTS
     
    If sh.AutoFilterMode Then sh.AutoFilterMode = False  'eliminate the filter, if any
    
    'drop the markers array content:
    sh.cells(procRng.cells(1).row + 1, lastC).Resize(UBound(arrMark), 1).Value2 = arrMark
    
    'filter by the marker column
    sh.Range(sh.cells(procRng.cells(1).row, 1), sh.cells(sh.UsedRange.rows.count, lastC)).AutoFilter lastC, "True"
End Sub

解决方案说明 #

直接用AutoFilter结合地址数组不可行，因为AutoFilter的筛选条件仅支持值匹配，无法通过单元格地址精准筛选保留首次出现的行。替代方案是：通过字典记录首次出现的单元格值，遍历指定区域的可见单元格，将重复值所在的行直接隐藏，全程无需辅助列，且保留后续普通筛选的能力。

无辅助列实现代码 #

Sub HideVisibleDuplicatesNoHelperColumn(procRng As Range)
    Dim visibleRng As Range, cell As Range
    Dim valueDict As New Scripting.Dictionary
    Dim headerRow As Long, sh As Worksheet
    Dim isFirstDataRow As Boolean
    
    Set sh = procRng.Parent
    headerRow = procRng.Cells(1).Row
    isFirstDataRow = True
    
    ' 获取指定区域的可见单元格
    On Error Resume Next
    Set visibleRng = procRng.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If visibleRng Is Nothing Then
        MsgBox "无效的可见区域"
        Exit Sub
    End If
    
    ' 遍历可见单元格，隐藏重复值的行（跳过表头）
    For Each cell In visibleRng.Cells
        ' 跳过表头行
        If cell.Row = headerRow Then
            Continue For
        End If
        
        ' 首次遇到非空值时标记（处理表头上方可能存在的空单元格）
        If cell.Value <> "" And isFirstDataRow Then
            valueDict.Add cell.Value, vbNullString
            isFirstDataRow = False
            Continue For
        End If
        
        ' 已存在的值则隐藏该行
        If valueDict.Exists(cell.Value) Then
            sh.Rows(cell.Row).Hidden = True
        Else
            ' 首次出现的值加入字典
            valueDict.Add cell.Value, vbNullString
        End If
    Next cell
End Sub

代码逻辑说明 #

1. 获取可见区域：仅处理指定区域内的可见单元格，避免影响已隐藏的行。
2. 字典去重：用Scripting.Dictionary记录首次出现的单元格值，确保只保留每个值的第一行。
3. 直接隐藏重复行：遍历过程中发现重复值时，直接隐藏对应行，无需辅助列。
4. 保留筛选能力：未使用AdvancedFilter或破坏原有筛选结构，后续可正常进行普通筛选操作。

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