CODEWITHSUNDEEP
excelslicersvba
Sorin Paul
Sorin Paul

Reputation: 5

VBA Slicer Select Blank

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

Upvotes: 0

Views: 1671

Answers (1)

Rik Sportel
Rik Sportel

Reputation: 2679

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

Upvotes: 0

Related Questions