Excel VBA AutoFilter多条件排除筛选报错及异常行为排查

问题根因 #

• Excel 的 AutoFilter 功能存在两处固有规则限制，是你遇到报错或条件失效的直接原因：
  1. xlFilterValues 运算符仅支持正向包含的数组条件，不支持传入多个<>开头的排除类数组参数，所以你第一段多变量代码直接触发1004错误。
  2. 当你改用xlAnd运算符时，AutoFilter单字段最多仅支持同时生效2个条件（Criteria1和Criteria2），传入超过2个的数组时系统只会读取最后一个值作为生效条件，因此仅A4的排除规则生效。
• 你之前用数组做包含筛选能正常运行，刚好是符合了xlFilterValues的适用场景，不存在逻辑冲突。

可行解决方案 #

方案1：构造反向包含筛选数组 #

思路是先提取目标列的所有不重复值，过滤掉你要排除的A1~A4，把剩下的值作为正向包含的筛选条件传入，符合xlFilterValues的使用规则。参考代码：

Dim excludeArr As Variant, dataArr As Variant, includeArr As Variant
Dim i As Long, dict As Object
Set dict = CreateObject("Scripting.Dictionary")

' 先把要排除的值存入字典
excludeArr = Array(Worksheets("Comparison Sheet").Cells(9, 4).Value, _
                   Worksheets("Comparison Sheet").Cells(10, 4).Value, _
                   Worksheets("Comparison Sheet").Cells(11, 4).Value, _
                   Worksheets("Comparison Sheet").Cells(12, 4).Value)

' 读取要筛选的D列所有值（对应Field=4）
dataArr = Sheets("Data Sheet").Range("D31:D279").Value

' 遍历收集不在排除列表里的不重复值
For i = 1 To UBound(dataArr)
    If IsError(Application.Match(dataArr(i, 1), excludeArr, 0)) Then
        If Not dict.exists(dataArr(i, 1)) Then
            dict.Add dataArr(i, 1), ""
        End If
    End If
Next i

' 构造包含数组执行筛选
If dict.Count > 0 Then
    includeArr = dict.keys
    Sheets("Data Sheet").Range("B$31:S279").AutoFilter Field:=4, Criteria1:=includeArr, Operator:=xlFilterValues
Else
    ' 所有值都要排除的情况，直接隐藏所有行
    Sheets("Data Sheet").Range("B$31:S279").Rows.Hidden = True
End If

方案2：辅助列法（更简单稳定，适合数据量不大的场景） #

1. 在Data Sheet的S列后新增T列作为辅助列，T31单元格输入公式：=COUNTIF('Comparison Sheet'!$D$9:$D$12,D31)=0，公式向下填充到T279。公式返回TRUE的行就是符合排除规则的行。
2. 直接对辅助列执行筛选即可：

Sheets("Data Sheet").Range("B$31:T279").AutoFilter Field:=19, Criteria1:="TRUE"

不需要处理复杂的数组逻辑，出错概率更低，排查也更方便。

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