How to clear all filters in a PivotTable using VBA?

To clear all filters in a PivotTable using VBA, you can loop through each PivotField in the PivotTable and set its `ClearAllFilters` method. This will reset any filtering that has been applied to your PivotTable. Here is a sample VBA code to accomplish this task:

Sub ClearAllFiltersInPivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    
    ' Set the worksheet containing the PivotTable
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change "Sheet1" to your sheet name
    
    ' Set the PivotTable you want to clear filters from
    Set pt = ws.PivotTables("PivotTable1")  ' Change "PivotTable1" to your PivotTable name
    
    ' Loop through each PivotField in PivotTable and clear filters
    For Each pf In pt.PivotFields
        pf.ClearAllFilters
    Next pf
    
    ' Option: refresh the PivotTable if needed
    ' pt.RefreshTable
    
    ' Message box to confirm action
    MsgBox "All filters in the PivotTable have been cleared."
End Sub

Instructions:

Open the VBA Editor: Press `ALT` + `F11` in Excel.
Insert a Module: Right-click on any workbook or sheet name in the “Project” navigation pane, select `Insert`, and then click `Module`.
Copy and Paste the Code: Copy the code provided above and paste it into the module.
Adjust Worksheet Name: Change `”Sheet1″` to the actual name of the worksheet containing your PivotTable.
Adjust PivotTable Name: Change `”PivotTable1″` to the actual name of your PivotTable.
Run the Macro: Press `F5` or go back to Excel and run the macro from the `Macro` dialog box (`ALT` + `F8`).

Important Notes:

Make sure the worksheet and PivotTable names in the code match exactly with those in your Excel workbook.
Ensure that the necessary VBA references are enabled if needed, and macros are allowed to run in your workbook for the VBA script to execute properly.
This script will clear all filters but will not disturb the overall layout or data within the PivotTable.

facebooklinkedin