Slicer Filter based on cell value

[deadlyliquidx]

Hello All,
I have a code that filters a olap based pivot table Slicers based on cell values in column A.

Sub SetSelection()
    Dim sc As SlicerCache
    Dim s As Slicer
    Set sc = ActiveWorkbook.SlicerCaches("Slicer_location") ' Name of slicer
     Set s = sc.Slicers(1)
    sc.VisibleSlicerItemsList = Array(ActiveSheet.Range("a1"), ActiveSheet.Range("a2:a2"), ActiveSheet.Range("a3:a3"))
End Sub

Problem is at "sc.VisibleSlicerItemsList = Array(ActiveSheet.Range("a1"), ActiveSheet.Range("a2:a2"), ActiveSheet.Range("a3:a3"))".
You see, exactly a1,a2,a3 has to have a slicer value.
If there is a blank, I get an error message.
IDEALLY, I would like to loop through column A (a1:A300) and stop when there is a blank.
And also instead of having to put range a1, range a2, range a3, i would like to have it just go through the whole named range storelist (a1:a300)

Basically I want it to go through the cells in column A and assign the value to the slicer until it hits the blank.

Thanks alot!!!
I would have provided a worksheet but my data is OLAP data so there is no connection.

 

[brt21]

by any chance u found a solution to this problem?