Too Large a Slicer List to Manually Select, need solution

[EvaPaul]

Dear All,

I am a mac excel user. Likely an easy fix for a seasoned slicer & VBA user. I have a large pivot table with an linked slicer, with approximately 20k records and growing (500 day). The slicer is fairly clunky to operate.

I need to be able to select a specific date range within the slicer without spending hours selecting all the records manually. Ideally, I would like to in two cells, manually enter the range (example: 1/08/18 ; 20/08/18) and this will then select all the records in the slicer which inturn updates the pivot-table.

If someone can help, most appreciated.

kind regards

Paul

 

[Kamolga]

When you click on you pivot, in the analyse tab, you have insert timeline below insert slicer (I am not sure where it is for MAC). In the timeline you can select from 1/8 to 20/8 in seconds. You can easily change units from months, to days, quarters or years on the timeline itself (without going through menus).

From my knowledge you would get quite a slow macro (especially with this growing database) as you would have to got through each date (pivotitem) and check that is bigger and smaller than the dates in the cells. Unless you keep the timeline (hidden if you don't want to see it) and try something like below where D1 is start date and E1 is end date

Dim Sdate As Date
Sdate = Range("D1").Value
Dim Fdate As Date
Fdate = Range("E1").Value
ActiveWorkbook.SlicerCaches("NativeTimeline_Date").TimelineState.SetFilterDateRange Sdate, Fdate

Note that you can get the macro launched automatically when you change the value in D1 or E1