Loop through workbook slicer names using VBA

Question

I have tried Googling and searching for this one but just can't quite get it. All I am trying to do is loop through the slicers on an activeworksheet and delete the slicer if it exists.

At the moment I have 6 slicers sitting there. Previously I had

    ActiveSheet.Shapes.Range(Array("Market Segment Name 2", "Line of Business 2" _
    , "Customer Name", "Product Group Name", "Product Type Name", "Product Code") _
    ).Select
    Selection.Delete

But this was no good if I had already deleted the slicers.

Now I am trying (note wb is set as a global variable in a module named "Public")

Option Explicit
Dim sl As Slicer
Dim slName As String

Set wb = ActiveWorkbook

For Each sl In wb.SlicerCaches
    If sl.Name = "Market Segment Name 2" Or _
        sl.Name = "Line of Business 2" Or _
        sl.Name = "Customer Name" Or _
        sl.Name = "Product Group Name" Or _
        sl.Name = "Product Type Name" Or _
        sl.Name = "Product Name" Then
        slName = sl.Name
        ActiveSheet.Shapes.Range(slName).Delete
    End If
Next sl

To me it seems like it should work. I have gotten it to work if I go down to SlicerItem level but I just can't figure out how to access it at Slicer level...

Any ideas would be greatly appreciated. Thank you.

If this fails I will have a go at building the array and deleting that way but I would still need a way of testing whether the slicer currently exists or not.

Answer 1

There are two ways that I can think of.

One is the Force method. Here we don't check if the slicer exists. We simply delete it if it exists. For example

On Error Resume Next
ActiveSheet.Shapes.Range("Market Segment Name 2").Delete
ActiveSheet.Shapes.Range("Line of Business 2").Delete
'
'~~> And so on
'
On Error GoTo 0

And the other method is to actually check if the slicer exists and then delete it. For example

Dim sl As SlicerCache

On Error Resume Next
Set sl = ActiveWorkbook.SlicerCaches("Market Segment Name 2")
On Error GoTo 0

If Not sl Is Nothing Then sl.Delete

'For Each sl In ActiveWorkbook.SlicerCaches
    'Debug.Print sl.Name
'Next

EDIT:

Followup from comments.

Converting the first code into a loop.

Sub Sample()
    Dim sSlicers As String
    Dim Myar
    Dim i As Long

    '~~> Slicers you want to delete
    sSlicers = "Market Segment Name 2,Line of Business 2"
    sSlicers = sSlicers & "," & "Customer Name,Product Group Name"
    sSlicers = sSlicers & "," & "Product Type Name,Product Code"

    '~~> Split the names using "," as a delimiter
    '~~> If your slicer names have "," then use a different delimiter
    Myar = Split(sSlicers, ",")

    For i = LBound(Myar) To UBound(Myar)
        On Error Resume Next
        ActiveSheet.Shapes.Range(Myar(i)).Delete
        On Error GoTo 0
    Next i
End Sub

Answer 2

Here is another peice of code I have finally figured out to do what I was originally trying to achieve.

Sub LoopSlicerNames()

Dim slCaches As SlicerCaches
Dim slCache As SlicerCache

Set slCaches = ThisWorkbook.SlicerCaches

For Each slCache In slCaches

    'MsgBox (slCache.Name & " is used for " & slCache.PivotTables.Item(1).Name)
    If slCache.PivotTables.Item(1).Name = "PTDashboard" Then
        slCache.Delete
        'MsgBox ("Slicer has been deleted")
    End If

    'If slCache.Name = "Slicer_Market_Segment_Name2" Then slCache.Delete

Next slCache

End Sub

The difference with this one is that I targeted the slicer based on its parent pivottable being on the worksheet so I wouldn't need to add the exact name of the slicer into the code. If I did want to add the name of the slicer into the code I can just switch around the commenting.

Thanks again Siddarth. You got me over the initial hurdles and provided good solutions. I think my initial problem was thinking that the slicer was the child of the slicercache. Looking into it now it seems to go slicerCaches, SlicerCache then SlicerItem and the 'slicer' as I referred to in my original question was the 'SlicerCache' level... so that picture I linked just confused the hell out of me haha.