How to clear slicer filters using VBA?

To clear slicer filters using VBA in Excel, you need to access the SlicerCache object associated with the slicer you want to clear. Here’s a step-by-step guide on how to do this:

Sub ClearSlicerFilters()
    Dim ws As Worksheet
    Dim sc As SlicerCache

    ' Set the worksheet you are working with
    Set ws = ThisWorkbook.Worksheets("Sheet1")  ' Change Sheet1 to your worksheet name

    ' Iterate through all slicers cache in the workbook to clear filters
    For Each sc In ThisWorkbook.SlicerCaches
        If sc.Slicers.Count > 0 Then
            If sc.Slicers(1).Shape.Parent Is ws Then
                sc.ClearManualFilter
            End If
        End If
    Next sc
End Sub

Open the Visual Basic for Applications (VBA) Editor:

Press `ALT` + `F11` to open the VBA editor in Excel.

Insert a Module:

In the VBA editor, go to `Insert` > `Module` to create a new module.

Write the VBA Code:

In the new module, you can write a macro to clear the slicer filter. Here’s an example code:

Customize the Code:

Change `”Sheet1″` to match the name of the worksheet where your slicers are located.
This code will clear the filter for all slicers on the specified worksheet.

Run the Macro:

Close the VBA editor and return to Excel.
To run the macro, go to `View` > `Macros` > `View Macros`, select `ClearSlicerFilters`, and then click `Run`.

This will clear the filters on all slicers on the specified worksheet. If you prefer to clear a specific slicer rather than all slicers on a sheet, you need to adjust the code to target a specific `SlicerCache` by its `Name` or `Index`.

Example for a specific slicer:

Sub ClearSpecificSlicer()
    Dim sc As SlicerCache

    ' Access the specific SlicerCache by name
    Set sc = ThisWorkbook.SlicerCaches("Slicer_SlicerName")  ' Replace Slicer_SlicerName with your slicer's cache name
    sc.ClearManualFilter
End Sub

Ensure you replace `”Slicer_SlicerName”` with the actual name of the slicer cache you want to clear. You can usually find this name by selecting the slicer in Excel and looking at its properties.

facebooklinkedin