Using VBA macro to select slicer item (but what if only one item exists)

[Jer Maine]

Good day, Excel masters. I'm a first time poster to MrExcel, hopefully not breaking any forum rules. I am trying to use a macro to filter a slicer with only two options: Y or N. I want to filter out all rows with N in a certain column. The slicer is connected to a table. There are no pivot tables or anything else. My code is as follows:

With ActiveWorkbook.SlicerCaches("Slicer_SB")
.SlicerItems("Y").Selected = True
.SlicerItems("N").Selected = False
Debug.Print "Visible Slicer Items: "
End With

This code works fine as long as the table contains both Y and N items. But if the table only contains Y's, then the N item disappears from the slicer, and my macro produces a run time error '5': Invalid procedure or argument and refers me to the line colored in red text above that refers to slicer item "N".

Is there a way to test to see if slicer item N exists so the code won't break?

I have tried many things and researched for hours without any answer so far. I have also tried commenting out the red line above, but then the code doesn't filter to just the Y's, it includes the Y's and N's.

 

[igold]

Hi Jer Maine,

Welcome to the Forum!

I poked around a little myself, and the only way around it that I could find was this. There may be a more eloquent way, but I did not see it. That said, this should work for you.

With wb.SlicerCaches("Slicer_SB")
    On Error GoTo Error_Handler
    .SlicerItems("Y").Selected = True
    .SlicerItems("N").Selected = False
Error_Handler:
    Debug.Print "Visible Slicer Items: "
End With

HTH

 

[Jer Maine]

Sorry for the late response, I was out of town. Your solution was brilliant and simple, I love it! Thank you so much. You also just taught me about Error Handling which is apparently quite useful.

 

[igold]

Great, I am glad it worked for you. Thanks for the feedback!