CODEWITHSUNDEEP
excelvba
Tart
Tart

Reputation: 305

How to clear all slicer only on one sheet?

I'm trying to clear all slicers on a specific worksheet but getting next error: "object variable or with block variable not set" in this line: cache.ClearManualFilter.

My code: 

Sub Clear_all_filters()

Dim cache As SlicerCache

Set mWS = Sheets("Specific_Sheet")

For Each cache In mWS.SlicerCaches
cache.ClearManualFilter
Next cache

End Sub

Upvotes: 2

Views: 7249

Answers (2)

Tart
Tart

Reputation: 305

Just in case someone's still looking for a solution to this question, here is the code I'm using now: 

Sub OnePageSlicersReset()

 Dim slcrC As SlicerCache
 Dim slcr As Slicer

 Application.ScreenUpdating = False

 For Each slcrC In ActiveWorkbook.SlicerCaches
   For Each slcr In slcrC.Slicers
      If slcr.Shape.Parent Is ActiveSheet Then
         If slcrC.FilterCleared = False Then
            slcrC.ClearManualFilter
            Exit For
         End If
      End If
   Next slcr
 Next slcrC

 Application.ScreenUpdating = True

 End Sub

Upvotes: 2

Dave
Dave

Reputation: 1643

Firstly, you should go to Tools>Options and turn on Require Variable Declaration. This will add Option Explicit to the top of any new module (You'll need to add it yourself to any pre-existing module).

This will force you to declare mWS, which would need to be a Worksheet class according to the Set command. This will bring up the error 'Method or data member not found' when you try to run the code.

This is because SlicerCaches are a property in the Workbook class, not Worksheet (As described here: https://learn.microsoft.com/en-us/office/vba/api/excel.slicercache)

In this case we can remove all reference to mWS and just use ThisWorkbook. As this suggests it will loop through all slicers in the current workbook so you may need to do some extra digging if you want to limit it to those in just one sheet.

Option Explicit

Sub Clear_all_filters()

Dim cache As SlicerCache

For Each cache In ThisWorkbook.SlicerCaches
    cache.ClearManualFilter
Next cache

End Sub

Upvotes: 0

Related Questions