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.