VBA De-Select All Slicer Options (except 1)

Question

The below code is giving an odd result. The slicer has 22 options (#1 at top going down to #22 on bottom).

If I currently have #12 selected, and then run the code, it will select slicer options 1-12. If X = the # of currently selected slicer option, the code will select 1 - X, and the options below stay un-selected. The above is just an example and not meant to show a natural, or desired, starting point.

Other info that may be relevant: Multiselect = True, The 2nd to bottom option = "", The Last option -"blank"

What I want the code to do is select the 3rd to last option, which is the first (from bottom) option that isnt blank or null data. This explains the commented out line.

However, I cannot figure out why the code below is not deselecting all options.

Sub Slicer()

Dim WB As Workbook
Set WB = ThisWorkbook
Dim i As Integer
Dim n As Integer



With WB.SlicerCaches("Slicer_Processed_date")
    n = .SlicerItems.Count

    For i = 1 To n
        If .SlicerItems(i).Selected = True Then
            .SlicerItems(i).Selected = False
        End If
    Next i

   '.SlicerItems(n - 2).Selected = True
End With   
End Sub

It seems pretty strange to filter a Slicer based on positions of items in it, rather than names. Can you elaborate further on your intent? — jeffreyweir, May 01 '18 at 00:41
The above macro is a sub procedure of a larger macro. The slicer items in question are dates and the macro needs to only select the most recent date. I don’t have much experience with dates in VBA so this may be a tedious work around due to lack of experience. The most recent date (after .refreshall) will always be the 3rd from bottom option so I worked with that. I’m not set on my approach, was just working with what I know. Absolutely open to a more direct approach to the above problem! — urdearboy, May 01 '18 at 03:15
Okay. As any great TV cook would say..."Here's one that I prepared earlier": https://stackoverflow.com/questions/39004607/filtering-pivot-table-with-vba/39069463#39069463 — jeffreyweir, May 01 '18 at 03:18
Might need some tweaking, and I might get a chance to have a tweak, but that should get you or some other reader started. — jeffreyweir, May 01 '18 at 03:19
Unfortunately, I don't understand a lot of that code, although I would like to. I can definitely tweak the below answer to work with mine (Turn #1 on, turn the rest off, turn the desired option on (#n-2), then turn #1 off). This should produce consistent results, ill have to check to make sure the 1st of the month works normally though. Are you aware if this approach will be slower than the more direct approach that you linked above? The slicer is a monthly tracker, so at most it will have 22 dates (business days in month). — urdearboy, May 01 '18 at 03:33

jeffreyweir · Accepted Answer · 2018-05-01T10:42:28.717

2

You can't deselect all items. You must leave one item visible at all times, or VBA will throw an error.

If you want off-the-shelf code to filter a Slicer on an array, check out my answer at How to update slicer cache with VBA

That array can have just the one thing in it if you like. And the comments in that code will help you understand how to filter a Slicer efficiently.

Edit: Now that I understand what you need, use this:

Sub SliceByIndex()


Dim sc As SlicerCache
Dim si As SlicerItem
Dim l As Long
Dim i As Long

Set sc = ThisWorkbook.SlicerCaches("Slicer_Test")

l = sc.SlicerItems.Count
With sc
    .PivotTables(1).ManualUpdate = True 'Stops PivotCache recalculating until we are done

    ' Select the first item, because one item MUST remain visible at all times.
    ' We'll unselected it when we're done
    .SlicerItems(1).Selected = True

    'Deselect everything else
    For i = 2 To l
        .SlicerItems(i).Selected = False
    Next i

    'Select the desired item
    .SlicerItems(l - 2).Selected = True

    'Deselect the first items
    .SlicerItems(1).Selected = False

    'Turn the PivotCache calculation on again
    .PivotTables(1).ManualUpdate = False
End With


End Sub

edited May 01 '18 at 10:42

answered May 01 '18 at 00:38

jeffreyweir

4,668
1
16
27

I'll be able to test this in a few hours. If I want the exact same code to run for 2 slicers, Can I just add it to the line "Set sc = Thisworkbook.SlicerCaches("Slicer_1", "Slicer_2") – urdearboy May 01 '18 at 16:17
No...you can't set one slicercache equal to one thing. Instead, you need to modify the code slightly so that you feed it an array, and then go `For each sc in SomeArray`. I'll amend the code today to show how. – jeffreyweir May 01 '18 at 19:49
That last comment should have said *You can't set one slicercache equal to **more than** one thing* – jeffreyweir May 02 '18 at 04:52
@urdearboy Do those two slicers share the same data source? Or are they completely different data sources? – jeffreyweir May 02 '18 at 04:54
@jeffreyweir hey, great solution but i have one problem. When my desired item is first item the code selects all items. How to solve this? – Tomz Dec 11 '20 at 11:06
Change the .SlicerItems(l - 2) bit to .SlicerItems(l) and remove the 'Select Desired Items' and 'Deselect the first item' lines. – jeffreyweir Dec 12 '20 at 23:19
Hmm dont work for me i changed the 'select the desired item from(l-2) to (sb_item) that is my desired item name. – Tomz Dec 15 '20 at 08:55
Please start a new question and post the link to it here and I'll see if I can take a look. No promises...I've got a lot going on currently. – jeffreyweir Dec 16 '20 at 09:03
Here it is, thanks in advance. https://stackoverflow.com/questions/65323644/loop-by-slicer-named-items – Tomz Dec 16 '20 at 12:51

VBA De-Select All Slicer Options (except 1)

1 Answers1

Linked