• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.

VBA: Finding selected slicer items without a loop

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)


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
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
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
    MsgBox s
End If
End Sub
Excel Version
First release
Last update
0.00 star(s) 0 ratings

More Excel articles from Worf

Some videos you may like

This Week's Hot Topics