使用数组多通配符筛选表格时部分值丢失的问题排查

需求 #

对大型表格的A列进行筛选，仅显示包含「VMI」「PRIORITY」「FAST」（不区分大小写）的行。

参考实现代码 #

参考现有代码实现如下：

Dim a As Long, aARRs As Variant, dVALs As Object, LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row

Set dVALs = CreateObject("Scripting.Dictionary")
dVALs.CompareMode = vbTextCompare

With Worksheets("Sheet1")
    If .AutoFilterMode Then .AutoFilterMode = False
    With .Range("A2:E" & LR)
        ' Build a dictionary so the keys can be used as the array filter '
        aARRs = .Columns(1).Cells.Value2
        For a = LBound(aARRs, 1) + 1 To UBound(aARRs, 1)
            Select Case True
                Case aARRs(a, 1) Like "*FAST*"
                    dVALs.Add key:=aARRs(a, 1), Item:=aARRs(a, 1)
                Case aARRs(a, 1) Like "*VMI*"
                    dVALs.Add key:=aARRs(a, 1), Item:=aARRs(a, 1)
                Case aARRs(a, 1) Like "*PRIORITY*"
                    dVALs.Add key:=aARRs(a, 1), Item:=aARRs(a, 1)
                Case Else
                    ' No match, do nothing '
            End Select
        Next a

        ' Filter on column A if dictionary not empty '
        If CBool(dVALs.Count) Then _
            .AutoFilter Field:=1, Criteria1:=dVALs.Keys, Operator:=xlFilterValues

    End With
End With

dVALs.RemoveAll: Set dVALs = Nothing

异常现象 #

• 部分符合条件的行未被筛选，即使测试大小写转换、辅助列筛选，仍遗漏前3行；
• 测试表中行3与行12仅开头字符不同，行12能被筛选出行3却不行；
• 新增一行与行3内容相同但「FAST」为大写后，行3竟能被筛选出来。

问题根源 #

1. 循环起始行错误：代码中循环从LBound(aARRs, 1) + 1开始，而aARRs是从A2单元格开始的数组，第一行对应A2，循环跳过了A2行的检查，导致该行即使符合条件也不会被加入字典；
2. 筛选逻辑错误：使用xlFilterValues配合字典的键进行筛选时，是精确匹配单元格完整内容，而非包含匹配。由于字典的CompareMode = vbTextCompare，大小写不同的相同内容会被视为同一个键，导致仅能筛选出其中一种大小写形式的行，其他大小写变体的行因内容不完全匹配而被遗漏。

修复方案 #

方案1：改用AdvancedFilter实现多通配符筛选 #

直接利用Excel的高级筛选功能，原生支持多通配符条件，无需字典，更高效：

Dim LR As Long
Dim ws As Worksheet
Dim criteriaRange As Range

Set ws = Worksheets("Sheet1")
LR = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

With ws
    If .AutoFilterMode Then .AutoFilterMode = False
    ' 临时创建条件区域（选空白列，避免干扰数据）
    Set criteriaRange = .Range("Z1:Z4")
    criteriaRange(1, 1).Value = ws.Range("A1").Value ' 匹配A列标题
    criteriaRange(2, 1).Value = "*FAST*"
    criteriaRange(3, 1).Value = "*VMI*"
    criteriaRange(4, 1).Value = "*PRIORITY*"
    
    ' 执行高级筛选
    .Range("A1:E" & LR).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=criteriaRange
    
    ' 清理临时条件区域
    criteriaRange.ClearContents
End With

方案2：修复原代码的循环与筛选逻辑 #

如果坚持使用原代码思路，修正循环起始行，并改用行标记方式筛选：

Dim a As Long, aARRs As Variant, LR As Long
Dim keepRows As Range
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
LR = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

With ws
    If .AutoFilterMode Then .AutoFilterMode = False
    With .Range("A2:E" & LR)
        aARRs = .Columns(1).Cells.Value2
        ' 遍历所有数据行（从数组第1行开始，对应A2）
        For a = LBound(aARRs, 1) To UBound(aARRs, 1)
            Select Case True
                Case aARRs(a, 1) Like "*FAST*", aARRs(a, 1) Like "*VMI*", aARRs(a, 1) Like "*PRIORITY*"
                    ' 标记符合条件的行
                    If keepRows Is Nothing Then
                        Set keepRows = .Rows(a)
                    Else
                        Set keepRows = Union(keepRows, .Rows(a))
                    End If
            End Select
        Next a
        
        ' 隐藏不符合条件的行
        .EntireRow.Hidden = True
        If Not keepRows Is Nothing Then keepRows.EntireRow.Hidden = False
    End With
End With

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