VBA Slicer Select Blank

Question

I have the following situation: I have a sheet with a slicer that should have only "blank" select. Sometimes i have only blank, other times I have blank and anywhere from 1 to 5 other options. My problem is that i cant force the macro to leave only blank selected regarless of the other options. My file is a template so i dont know if people will leave on blanks or select anything else.

i have had several attepts:

With ActiveWorkbook.SlicerCaches("Slicer_OOS_Reason_2017___Performance")

.SlicerItems("(blank)").Selected = True

it doesnt work when i have others than blank :(

   With ActiveWorkbook.SlicerCaches("Slicer_OOS_Reason_2017___Performance")
    .SlicerItems("(blank)").Selected = True
    .SlicerItems("option1").Selected = False
    .SlicerItems("option2").Selected = False
    .SlicerItems("option3").Selected = False
    .SlicerItems("option4").Selected = False
    .SlicerItems("option5").Selected = False
End With

this one work perfectly only if all options are available

if one option is not in the slicer i get "Invalid procedure call or argument"

I saw other codes but i was a little lost. I need a simple code, i dont need anything else then to select blank every single time:)

I dont need to iterate each options from my list, i need a single option to be select regarless of the number and name of the options available

Later edit: The slicer i use dont show old delete data even if the option is select in the slicer.

thanks, sorin

Possible duplicate of Iterate over slicer via VBA and select a single item each time — Luuklag
I have looked at this posting and its not the same thing. i dont want to select each item at a time, i want only 1 item select — Sorin Paul
You misread the title, it says a single item, not each single item. — Luuklag
I thing the code was to much for me and didnt get it. i didnt understand where to put my "blank". — Sorin Paul

Rik Sportel Rik Sportel · Accepted Answer · 2017-10-23T09:08:24

Try looping over the items and just select the blank one:

Dim si as SlicerItem
With ActiveWorkbook.SlicerCaches("Slicer_OOS_Reason_2017___Performance")
For Each si in .SlicerItems
    If si.Name = "(blank)" Then
        si.Selected = True
    Else
        si.Selected = False
    End If
Next si
End With

VBA Slicer Select Blank

1 Answers