Detecting Whether Value is in Slicer

Hi guys

Problem: I have a list of cities. Some of these cities have population data and some don't. I have a slicer which is related only to the cities which do have population data (i.e. cities with no data do not appear in the slicer). Basically, I have written a macro which goes through the total list of all cities and, one by one, selects them in the slicer and reports a value from an associated PivotTable. However, this doesn't entirely work as the macro encounters cities which are not in the slicer and gives me an error.

Question: How could I write VBA code to detect whether a city is in the slicer or not? Ideally, I want something like 'If City is in slicer, then...'.

Thanks in advance!

Thanks very much, Olly! I think I see the logic in what you're suggesting. I've tried doing this myself by copying/pasting your code but it is giving me an error saying application-defined or object-defined error. This is on the 'For Each si In ThisWorkbook.SlicerCaches(sSlicer).SlicerItems' line. Do you know why? Could it be that 'si' isn't defined as anything (when I hover over it in debug mode, it says si = nothing)?

I've fiddled around with my workbook and found that I was defining various variables in a weird way which probably didn't help. After fixing this, I think that your code should work. For my needs, I have used an 'On Error GoTo' command to bypass all cities which don't have data (as these are the only source of error in my macro) and just continue onto the next city.

Thank you very much for your help, Olly!