Loop through workbook slicer names using VBA

Viewed 8402

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.

2 Answers
Related