• If you would like to post, please check out the MrExcel Message Board FAQ and register here. 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.
Worf

VBA: Finding selected slicer items without a loop

Excel Version
  1. 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)

dmodel.PNG


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
  • Like
Reactions: cuchi33
Author
Worf
Views
3,296
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from Worf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top