• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Slicer Selection via VBA

Dokat

Member
Good Morning,

I have below macro code that selects Henkel as a manufacturer in a slicer. However my issue is code takes too long to run. Looks for each manufacturer in slicer and checks for True or False. I was wondering if there is a way to optimize the code where it doesnt take 5 minutes to execute. I have total of 7 slicers and i only want to select Henkel in Manufacturer1 slicer clear all other filters in other slicers. Is there a way to optimize the code to run faster? Thank you.

Code:
Sub HenkelOnly()
'
' HenkelOnly Macro
'

'
With ActiveWorkbook.SlicerCaches("Slicer_Manufacturer1")
    .ClearManualFilter
    For Each oSlicerItem In .SlicerItems
        If oSlicerItem.Name = "HENKEL" Then
            oSlicerItem.Selected = True
        Else
            oSlicerItem.Selected = False
        End If
    Next oSlicerItem
End With

End Sub
 
Is your slicer loaded from Data Model? If so, there is more efficient way of doing this. But will need sample workbook to understand your data model structure.

If Slicer is based on PivotTable with Table as data source. I don't think there is more efficient code. But the process shouldn't take 5 min...

One thing you can try is to set ".ManualUpdate" property of all PivotTables connected to the slicer to "True" before making selection(s) on the Slicer. And turn it back to "False" at the end.
 
Hi Chihiro,

Thanks for your response. I appreciate that. Unfortunately Slicer is based on Pivot table. Where can i make the change for ".ManualUpdate" in slicer settings or pivot table settings?

But pivot table is off a data model not sureif this makes a difference? Thanks
 
If pivot table is based on data model. Slicer created from that pivot table will also be based on data model.

Use macro recorder and record the code when "HENKEL" is selected on the slicer.

It should give you something like...
Code:
Sub Macro1() 
ActiveWorkbook.SlicerCaches("Slicer_Manufacturer1").VisibleSlicerItemsList = Array( _
        "[Query1].[Manufacturer1].&[HENKEL]")
End Sub

Where Query1 is your data model name, followed by column/field name and item name.
 
To set all pivot tables to manual update mode, insert code below before your operation.
Code:
Dim pvt As PivotTable
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    For Each pvt In ws.PivotTables
        pvt.ManualUpdate = True
    Next pvt
Next ws

And then you'd use the same code (excluding variable declaration) and set it back to "False" at end of your code.
 
Yes i tried recording a macro however it works with same logic it checks all the manufacturer names until it finds a match for Henkel. There are too many manufacturer names to check which causing macro to run slow.

Thanks
 
If you can upload a sample workbook. I can take a look at it.

Edit: If Macro recorder gives you multiple ".SlicerItems("xxx").Selected = True/False" lines, that means your pivot table & slicers are not from data model.
 
Last edited:
All i want to do is to when click on Purex clear all slicers and select "Purex" under brand slicer and if anything else is already pre selected clear it and select purex.

Thank you for your help.

Here is the link to download the file
https://ufile.io/24419
 
Hmm, I'd strongly recommend first loading connection via PowerQuery if you have it. Then load to data model and base your pivots on that. It would dramatically speed-up code for slicer contorl...

Unfortunately SlicerItems doesn't allow use of names in array and need to be looped. Fastest I've got it down to was around 1 min.
 
The constrain with data model is if the source data changes i can refresh it all and update thats why i've been avoiding to load to data model.

1 min. is much shorter than what its taking right now. How did you do that?

Thanks
 
thank you . Also what would be the code if Purex is selected all other brands listed in the slicer = False? I am thinking this might speed it up since it doesnt have to go thru each code.
Code:
  With ActiveWorkbook.SlicerCaches("Slicer_Brand")
    .SlicerItems("PUREX").Selected = True
all other brands = False. Currently there is a line for all other brands.
For Example:
Code:
  With ActiveWorkbook.SlicerCaches("Slicer_Brand")
    .SlicerItems("TIDE").Selected = FALSE
all other brands = False. Currently there is a line for all other brands.
 
Last edited:
Unfortunately, for SlicerItems. You have to set ".Selected" True/False for each one. There's no known workaround to my knowledge.

If pivot tables and slicers are based on data model, you can use ".VisibleSlicerItemsList" property of SlicerCache. Which can set single item as selected and all others unselected at same time.
 
Sounds like running pivot off data model is my best option but i am afraid i wont be able to change the source data and update data model weekly. My understanding once you create the data mode you cant refresh and update with new data. Thanks Chiriro
 
Back
Top