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 lik…

Accepted Answer

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
```

Loop through workbook slicer names using VBA

Solution

Problem