Geek Answers Handbook

Scroll through the list of book slicers using VBA

I tried Googling and searched for this one, but just can't get it. All I'm trying to do is thin out the slicers on the activeworksheet page and delete the slicer if it exists.

At the moment I have 6 carvers. I used to have

    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 it wasn’t good if I already deleted the slicers.

Now I'm trying (note that 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

It seems to me that it should work. I got it working if I go to the SlicerItem level, but I just can't figure out how to access it at the Slicer level ...

Any ideas would be highly appreciated. Thanks.

, , , .

+4
2

, .

- Force. , . , .

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

- , , .

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

.

.

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
+5

, , , , .

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

, slicer pivottable , slicer . slicer , .

. . , , slicer slicercache. , , , slicerCaches, SlicerCache, SlicerItem "slicer", , "SlicerCache"... , , .

+2

More articles:


All Articles