vba 循环遍历每个工作表并取消过滤所有表格的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24533570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Macro to loop through each worksheet and unfilter all tables
提问by
I wrote some very simple code that just loops through each sheet in a workbook, and then removes filtering (if activated) by using ActiveSheet.ShowAllData.
我编写了一些非常简单的代码，它只是循环遍历工作簿中的每个工作表，然后使用 ActiveSheet.ShowAllData 删除过滤（如果已激活）。
Here is the (updated) code:
这是（更新的）代码：
Sub removeFilters()
'This macro removes any filtering in
'order to display all of the data but it does not remove the filter arrows
On Error GoTo Errorcatch
For i = 1 To ActiveWorkbook.Worksheets.Count
If ActiveSheet.Visible = True Then
ActiveSheet.ShowAllData
End If
Next i
Exit Sub
Errorcatch:
MsgBox Err.Description
End Sub
The code appears to work alright, but it ends with an error, and then a MsgBox displays. Here is a screenshot of the MsgBox:
该代码似乎工作正常，但以错误结束，然后显示 MsgBox。这是 MsgBox 的屏幕截图：
The workbook has some hidden sheets, and I want to skip them entirely, which is why I added the Ifstatement.
工作簿有一些隐藏的工作表，我想完全跳过它们，这就是我添加If语句的原因。
采纳答案by Patrick Honorez
ShowAllData throws an error if autofilter is not activated, so:
如果未激活自动过滤器，则 ShowAllData 会引发错误，因此：
Dim ws As Worksheet
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
If ws.Visible Then ws.ShowAllData
Next ws
on error goto 0 'or to hell or whatever
回答by Rory
If you are dealing with Tables:
如果您正在处理表格：
Sub removeFilters()
Dim ws As Worksheet
Dim oList As ListObject
'This macro removes any Table filtering in
'order to display all of the data but it does not remove the filter arrows
For Each ws In ActiveWorkbook.Worksheets
For Each oList In ws.ListObjects
oList.AutoFilter.ShowAllData
Next oList
Next ws
End Sub
