Excel VBA 工作表多表格清除自动筛选仅首个生效问题问询

你代码中的清除筛选逻辑使用了ActiveSheet.AutoFilter.ShowAllData，该工作表层级的方法仅会作用于工作表内第一个创建的结构化表格（ListObject），其余表格的筛选状态不会被该命令修改，这就是你点击取消隐藏时只有第一个表格筛选被清除的核心原因。

你需要逐个对所有已经声明的表格对象执行清除筛选操作，同时可以删除不必要的单元格选择操作避免界面闪屏，修复后的完整代码如下：

Private Sub OpAssumptionsToggle_Click()
    ' 建议在模块顶部加上Option Explicit强制变量声明，避免变量未定义错误
    Dim ActIncAss As ListObject, ActExpAss As ListObject
    Dim S1IncAss As ListObject, S1ExpAss As ListObject
    Dim S2IncAss As ListObject, S2ExpAss As ListObject
    Dim PFIncAss As ListObject, PFExpAss As ListObject
    
    ' 初始化表格对象
    Set ActIncAss = ListObjects("ActualIncomeAssumptions")
    Set ActExpAss = ListObjects("ActualExpenseAssumptions")
    Set S1IncAss = ListObjects("S1IncomeAssumptions")
    Set S1ExpAss = ListObjects("S1ExpenseAssumptions")
    Set S2IncAss = ListObjects("S2IncomeAssumptions")
    Set S2ExpAss = ListObjects("S2ExpenseAssumptions")
    Set PFIncAss = ListObjects("PFIncomeAssumptions")
    Set PFExpAss = ListObjects("PFExpenseAssumptions")
        
    If ActiveSheet.FilterMode Then
        ' 逐个清除所有表格的筛选
        If ActIncAss.AutoFilter.FilterMode Then ActIncAss.AutoFilter.ShowAllData
        If ActExpAss.AutoFilter.FilterMode Then ActExpAss.AutoFilter.ShowAllData
        If S1IncAss.AutoFilter.FilterMode Then S1IncAss.AutoFilter.ShowAllData
        If S1ExpAss.AutoFilter.FilterMode Then S1ExpAss.AutoFilter.ShowAllData
        If S2IncAss.AutoFilter.FilterMode Then S2IncAss.AutoFilter.ShowAllData
        If S2ExpAss.AutoFilter.FilterMode Then S2ExpAss.AutoFilter.ShowAllData
        If PFIncAss.AutoFilter.FilterMode Then PFIncAss.AutoFilter.ShowAllData
        If PFExpAss.AutoFilter.FilterMode Then PFExpAss.AutoFilter.ShowAllData
    Else
        ' 批量给所有表格添加筛选
        ActIncAss.Range.AutoFilter Field:=6, Criteria1:="<>0"
        ActExpAss.Range.AutoFilter Field:=6, Criteria1:="<>0"
        S1IncAss.Range.AutoFilter Field:=6, Criteria1:="<>0"
        S1ExpAss.Range.AutoFilter Field:=6, Criteria1:="<>0"
        S2IncAss.Range.AutoFilter Field:=6, Criteria1:="<>0"
        S2ExpAss.Range.AutoFilter Field:=6, Criteria1:="<>0"
        PFIncAss.Range.AutoFilter Field:=6, Criteria1:="<>0"
        PFExpAss.Range.AutoFilter Field:=6, Criteria1:="<>0"
    End If
    
    ' 清除剪切板状态并定位到A1
    Application.CutCopyMode = False
    Range("A1").Select
End Sub

如果后续你还会新增表格，且所有表格都需要筛选第6列，也可以直接遍历当前工作表的所有ListObject来执行操作，不需要逐个声明表格对象，代码可维护性更强：

Private Sub OpAssumptionsToggle_Click()
    Dim tbl As ListObject
    If ActiveSheet.FilterMode Then
        For Each tbl In ActiveSheet.ListObjects
            If tbl.AutoFilter.FilterMode Then tbl.AutoFilter.ShowAllData
        Next tbl
    Else
        For Each tbl In ActiveSheet.ListObjects
            tbl.Range.AutoFilter Field:=6, Criteria1:="<>0"
        Next tbl
    End If
    Application.CutCopyMode = False
    Range("A1").Select
End Sub

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