Excel VB Code to Set Filter to a range of Options

Question

I currently have a Pivot Table that pulls its data from a SharePoint site. With this Pivot table I want to use a Button to set the Filter to a certain date range. I used this site to get the filter to change the Filter value to a certain date, code below:

Sheets("Sheet2").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = Date

This works well to reset the filter to the current days date, but I can't seem to find any way to make it set the filter to all the dates for the past 5, 10, or 15 days, or even to set the filter to all the dates for the current month.

Any assistance would be greatly appreciated.

Can you post the sample data, much easier to test your code with that — Tyler Cowan, Dec 18 '15 at 22:00

Scott Holtzman · Accepted Answer · 2015-12-21T19:57:39.780

0

Given that you are trying to apply a date range to your ReportFilter (as evidenced with .CurrentPage = Date, you will need to loop through all the PivotItems in the Date PivotFields and test against your date range.

Something like this below (untested):

Dim ws as Worksheet, pt as PivotTable, pf as PivotField, pi as PivotItem
Dim dStart as Date, dEnd as Date

dStart = #1/1/2015#
dEnd = #1/31/2015#

Set ws = Sheets("Sheet2")
Set pt = ws.PivotTables("PivotTable1")
Set pf = pt.PivotFields("Date")

With pf
   .ClearAllFilters
   For each pi in pf.PivotItems
      If pi.Value > = dStart And pi.Value <= dEnd Then 
         pi.Visible = True 'or maybe pi.Selected = True
      Else
         pi.Visible = False 'or maybe pi.Selected = False
      End If
   Next
End With

edited Dec 21 '15 at 19:57

answered Dec 18 '15 at 22:05

Scott Holtzman

27,099
5
37
72

Scott: Iterating through PivotItems is incredibly slow: as per my post at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ this might take many minutes even for a small PivotTable, and even if you turn pt.ManualUpdate = True before running all the following snippets, so that the PivotTable doesn’t try to update after each and every change. – jeffreyweir Dec 19 '15 at 18:51
Sorry, that should have read: Iterating through PivotItems is incredibly slow: as per my post at dailydoseofexcel.com/archives/2013/11/14/… this might take many minutes for a moderately large PivotField (e.g. say 20,000 items in the field) even if you set pt.ManualUpdate = True while you run the code. Granted, you don't often come across PivotTables where there are more than a few hundred discrete dates. But in general, it pays to steer clear from iteration when you can instead use a 'between' filter. – jeffreyweir Dec 19 '15 at 20:26
@jeffreyweir - Please remove your down vote. In the code the OP demonstrates that he is asking about Filtering a `ReportFilter` (see `.CurrentPage = Date`) as opposed to a filter on the `Column` or `Row Labels`. If it was in the `Column / Row Labels`, your comment would make sense, but given that you cannot use `Between` when filtering dates in the `ReportFilter` section, your comment does not apply. I did, however, make my answer more clear thanks to your note, which helped :) – Scott Holtzman Dec 21 '15 at 19:56
@ScottHoltzman – Thank you. What you provided worked great. I was even able to tinker with it a bit and get it to pull in the current date and current date – 5, 10, or 15 days. The only thing I have left to solve is a type mismatch error that occurs when the filtering is completed. Any advice? – Emmerson Dec 21 '15 at 21:26
@Emmerson - It's impossible to have any idea about *a type mismatch error that occurs when the filtering is completed* without any real context. If you can't find the source and solution of your error, please post a new question to SO and some one will look at :) – Scott Holtzman Dec 21 '15 at 21:30
1

Scott: you are right. Timelines allow you to set a Between date, so I'll post some code that uses them shortly. However, your code can be made noticeably more efficient on sizeable pivots. First, you should set .ManualUpdate to TRUE before the loop, or the PivotTable will refresh after each and every item is hidden/unhidden. And second, you should test whether the .visible status needs to be changed at all, given it takes significantly longer to change the .visible status than to read it. Your code works, but two simple tweaks will make it work an order of magnitude more efficiently. – jeffreyweir Dec 21 '15 at 23:30
Thinking about this some more, the most efficient way to filter the OP's PivotTable on a date range is to simply remove the Date field from the PivotTable altogether. Even though it's not in the PivotTable, you still have access to all the exact same filtering options as you would if you dragged it to the Rows or Columns areas. Again, this is going to let you filter the PivotTable on date ranges far more efficiently than any other approach. – jeffreyweir Dec 22 '15 at 03:54

jeffreyweir · Answer 2 · 2015-12-22T04:11:55.890

Emmerson: You don't say what version of Excel you have. I'm using Excel 360, which gives me several ways to filter a PivotTable on a date range directly.

First, if your date field happens to be in the Rows or Columns area (but not the Filters/Page area as in your particular case) there's the Date Filters option available from the Filter dropdown:

And secondly, in Excel 2013 or later there are timelines:

As Scott rightly says in the comments above, the first option only works directly if the PivotField you want to filter is not a PageField i.e. it is not in the Filters pane of the PivotTable Fields list. Because if it is in the Filters pane, then you don't get any of the filter options shown above, as evidenced in the following screenshot:

But there's an easy workaround: simply drag it out of the PivotTable altogether, because you can actually still filter on a field that isn't in the PivotTable:

Firing up the macro recorder as I set that 'Between' filter resulted in the following code:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add2 _
        Type:=xlDateBetween, Value1:="23/12/2015", Value2:="31/12/2015"

Here's a generalized version of that, that will filter any date pivotfield to whatever last X you want:

Sub LastXDays(lDays As Long, Optional pf As PivotField)

Dim StartDate As Date
Dim EndDate As Date
EndDate = Date
StartDate = EndDate - lDays + 1
If pf Is Nothing Then
    On Error Resume Next
    Set pf = ActiveCell.PivotField
    If Err.Number <> 0 Then GoTo errhandler

    On Error GoTo errhandler
End If
If Not IsDate(pf.PivotItems(1)) Then GoTo errhandler
pf.ClearAllFilters
pf.PivotFilters.Add2 _
    Type:=xlDateBetween, _
    Value1:=CStr(StartDate), _
    Value2:=CStr(EndDate)

errhandler:

End Sub

You would call that routine using something like this:

Sub Last5Days()
LastXDays 5, ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
End Sub

Post back if that's unclear of if you need more help implementing this.

Excel VB Code to Set Filter to a range of Options

2 Answers2