- Excel Version
- 2016
Sometimes you have slicers with a long list of items, and need to find out what are the selected items. Normally, it is necessary to loop through all items to get this information, as shown on the first code.
However, if the pivot table is created with the Data Model option checked, it will be OLAP based thus allowing the second method, which loops only the array containing the desired slicer items. If no item is selected it will inform that all are displayed.
Note that this kind of pivot table does not support groups, calculated fields or calculated items.
Overview of Online Analytical Processing (OLAP)
However, if the pivot table is created with the Data Model option checked, it will be OLAP based thus allowing the second method, which loops only the array containing the desired slicer items. If no item is selected it will inform that all are displayed.
Note that this kind of pivot table does not support groups, calculated fields or calculated items.
Overview of Online Analytical Processing (OLAP)
VBA Code:
Sub First()
Dim MyArr(), i%, s$, dest As Range
Set dest = [p200] ' starting cell
For i = 1 To ThisWorkbook.SlicerCaches.Count ' all slicers
s = ThisWorkbook.SlicerCaches(i).Name
If s Like "*X*" Then ' desired slicers
MyArr = IL(s)
Set dest = dest.Resize(1, UBound(MyArr) + 1)
dest.Value = MyArr ' to worksheet
Set dest = dest.Offset(1)
End If
Next
End Sub
Public Function IL(sn$) ' loop all items
Dim ShortList(), i%, sc As SlicerCache, sI As SlicerItem
i = 0
Set sc = ThisWorkbook.SlicerCaches(sn)
For Each sI In sc.SlicerItems
If sI.Selected = True Then 'And sI.HasData = True
ReDim Preserve ShortList(i)
ShortList(i) = sI.Value
i = i + 1
End If
Next
IL = ShortList
End Function
Sub Second()
Dim vs, i%, sc As SlicerCache, s$
s = ""
Set sc = ActiveWorkbook.SlicerCaches("Slicer_person")
If sc.OLAP Then
vs = sc.VisibleSlicerItemsList
For i = LBound(vs) To UBound(vs)
s = s & vs(i) & vbLf
Next
MsgBox s
End If
End Sub
