Filter a pivot slicer on dates - working, but is there a quicker way?!

[dancing-shadow]

Hello,

I have the below bit of code within a larger piece of code which is working as intended - looping through each slicer item, and selecting/deselecting if the date meets the specified criteria.
My issue is that as my data increases, there are more and more dates to loop through, and it's obviously taking longer and longer to complete. I tried adding in the xlCalculationManual/Automatic, thinking that would stop the pivots updating after each individual date is dealt with, but it hasn't helped at all. I'm wondering if there is any other way at all I can make this run faster? If I stop the macro at the first Application.Calculation, manually select/deselect the slicer dates myself and resume the code from the second Application.Calculation, the whole code in it's entirety takes around 3-5 mins to run. By letting it cycle through the slicer items itself it's taking upwards of an hour... I can't can't fathom quite why it takes so long to look at a date, determine if it meets the criteria, deal with it and move on.

For some background info, I cannot let the pivots unfilter all dates and re-filter on the dates between x and y criteria, as the layout of the pivots means there is not enough room to allow the pivot to expand before collapsing again - basically on the left there is a pivot for daily totals, and right next to it on the right is a pivot for monthly totals. There are around 15 of these pairs of pivots altogether of varying sizes (rows), and I cannot amend the layout due to client specifications - hence using the slicer, as this deselects some dates before reselecting others so the total amount of dates selected never exceeds 30.

Dim today As Date
Dim startdte As Date


today = Date
startdte = today - 30


Application.Calculation = xlCalculationManual


    For Each item In ActiveWorkbook.SlicerCaches("Slicer_due_abs").SlicerItems
        If item.Name = "(blank)" Then
            item.Selected = False
        ElseIf item.Name >= startdte And item.Name <= today Then
                item.Selected = True
                    Else
                item.Selected = False
            End If
    
    Next item
    
Application.Calculation = xlCalculationAutomatic

 

[RedBeard]

This might be of use: https://stackoverflow.com/questions...nce-updating-excel-slicer-selection-using-vba