Removing Filters for Each Pivot Table in a Workboo

2019-07-20 04:04发布

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.

标签: excel vba
3条回答
三岁会撩人
2楼-- · 2019-07-20 04:40

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

(I would have just commented on the other answer, but I need 50 "reputation" -_- )

查看更多
兄弟一词,经得起流年.
3楼-- · 2019-07-20 04:42

7-line solution This will show all data without removing the filter, and won't fail on unfiltered sheets.

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.

To remove each filter entirely, just replace lines 4+5 with Cells.AutoFilter.

查看更多
干净又极端
4楼-- · 2019-07-20 05:01

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
查看更多
登录 后发表回答