vba 删除工作簿中每个数据透视表的过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28545426/
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
Removing Filters for Each Pivot Table in a Workbook
提问by GregdeLima
I'm trying to sort through each table within each worksheet in an active workbook and reset all the filters.
我正在尝试对活动工作簿中每个工作表中的每个表进行排序并重置所有过滤器。
Note that each table is a pivot table.
请注意，每个表都是一个数据透视表。
Sub ResetFilters()
Dim ws As Worksheet
Dim wb As Workbook
Dim listObj As ListObjects
For Each ws In ActiveWorkbook.Worksheets
For Each listObj In ws
With ActiveSheet.listObj.Sort.SortFields.Clear
End With
Next listObj
Next ws
End Sub
Error received: "Object doesn't suppor this property or method" on Line 7.
在第 7 行收到错误：“对象不支持此属性或方法”。
回答by Rory
Assuming you mean clear filters and remove sorting, you can use:
假设您的意思是清除过滤器并删除排序，您可以使用：
Sub ResetFilters()
Dim ws As Worksheet
Dim wb As Workbook
Dim listObj As ListObject
For Each ws In ActiveWorkbook.Worksheets
For Each listObj In ws.ListObjects
If listObj.ShowHeaders Then
listObj.AutoFilter.ShowAllData
listObj.Sort.SortFields.Clear
End If
Next listObj
Next ws
End Sub
回答by lordelliott
This worked for with a small change to the previous answer. Just use the ShowAutoFilter property instead of ShowHeaders. ShowAutoFilter is the property that references the these things: Screencap of AutoFilter Dropdown Box in Excel Table
这适用于对先前答案的小改动。只需使用 ShowAutoFilter 属性而不是 ShowHeaders。ShowAutoFilter 是引用这些东西的属性：Excel 表格中 AutoFilter 下拉框的屏幕截图
(I would have just commented on the other answer, but I need 50 "reputation" -_- )
（我只是对另一个答案发表评论，但我需要 50 个“声誉”-_-）
回答by Finger Picking Good
7-line solutionThis will show all data without removing the filter, and won't fail on unfiltered sheets.
7 行解决方案这将显示所有数据而无需删除过滤器，并且不会在未过滤的工作表上失败。
Sub RemoveFilters()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
ws.ShowAllData
Next ws
End Sub
The poster's VBA might have failed on encountering an unfiltered sheet.
海报的 VBA 可能在遇到未过滤的工作表时失败。
To remove each filter entirely, just replace lines 4+5 with Cells.AutoFilter.
要完全删除每个过滤器，只需将第 4+5 行替换为Cells.AutoFilter.
