如何通过VBA实现自动筛选仅保留Pending值及空白项？

问题说明 #

需要对第5列（E列）筛选仅保留“Pending”项；若报表无“Pending”，则只显示空白内容和列标题。但现有代码运行后总会残留“Inactive”项，用=匹配空白的方法无效。

当前代码 #

Sub PepperH()
Worksheets("Sheet2").Select
Range("A9:L3000").AutoFilter Field:=5, Criteria1:="Pending", Operator:=xlOr, Criteria2:="="
'Should only show pending accounts- headers run A1-A8 so we need to start after that
End Sub

解决方案 #

核心思路是先判断目标值是否存在，再动态设置筛选条件，规避Excel自动筛选的强制保留内容逻辑：

修改后的代码：

Sub PepperH()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim pendingCount As Long
    
    ' 绑定目标工作表，避免使用Select操作
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    Set dataRange = ws.Range("A9:L3000")
    
    ' 清除之前的筛选状态，防止条件叠加
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    ' 统计第5列中"Pending"的出现次数
    pendingCount = Application.WorksheetFunction.CountIf(dataRange.Columns(5), "Pending")
    
    ' 根据统计结果设置筛选规则
    If pendingCount > 0 Then
        ' 存在"Pending"项，直接筛选该值
        dataRange.AutoFilter Field:=5, Criteria1:="Pending"
    Else
        ' 不存在"Pending"项，筛选空白单元格
        dataRange.AutoFilter Field:=5, Criteria1:="=", Operator:=xlFilterValues
    End If
End Sub

关键修改点 #

• 移除Select操作，直接绑定工作表和数据区域，提升代码稳定性与执行效率
• 先清除原有筛选状态，避免旧条件干扰新筛选逻辑
• 用CountIf判断目标值是否存在，动态切换筛选规则
• 筛选空白时使用Operator:=xlFilterValues，确保Excel正确识别空白单元格，彻底清除不符合条件的残留项

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