CODEWITHSUNDEEP
excelvba
Sven
Sven

Reputation: 301

Clear a Specific Slicer on the Active Worksheet

I have have two slicers on a worksheet. One is named Region, the other is named Hospital. I am trying to create two buttons on the page. One that will clear ONLY the Hospital slicer; the other to clear ONLY the Region slicer on the active worksheet only. I have a button that will clear ALL of the slicers on the active worksheet (code below), but I am struggling to get it to work only with the specific slicer.

Sub ClearSlicer()

'
'Clears Slicer on main dashboard page
'

Dim Slcr As SlicerCache
Dim slc As Slicer

Application.ScreenUpdating = False

For Each Slcr In ActiveWorkbook.SlicerCaches
    For Each slc In Slcr.Slicers
        If slc.Shape.Parent Is ActiveSheet Then
           Slcr.ClearManualFilter
           Exit For
        End If
    Next slc
Next Slcr

Application.ScreenUpdating = True

End Sub

I have tried adding ActiveWorkbook.SlicerCaches("Hospital") but this did not seem to work either. This threw a debug error which I was not able to correct.

I have searched and yet to find this on here, so I apologize if I happened to miss it. Any assistance here would be helpful. Thank you

Upvotes: 1

Views: 1723

Answers (1)

BigBen
BigBen

Reputation: 50008

You can refer to a Slicer by .Name:

Sub ClearSlicer()

'
'Clears Slicer on main dashboard page
'

Dim Slcr As SlicerCache
Dim slc As Slicer

Application.ScreenUpdating = False

For Each Slcr In ActiveWorkbook.SlicerCaches
    For Each slc In Slcr.Slicers
        If slc.Name = "Region" Or slc.Name = "Hospital" Then
            Slcr.ClearManualFilter
        End If
    Next slc
Next Slcr

Application.ScreenUpdating = True

End Sub

If you're not exactly sure on the slicer name, perhaps use Like:

If slc.Name Like "*Region*" Or slc.Name Like "*Hospital*" Then

(though you might then need to check whether the slicer is on the ActiveSheet as you currently do).

Upvotes: 2

Related Questions