Filter pivot table row label using Excel VBA

0
votes

How can I filter a row label in a pivot table, using Excel VBA, to show only values that are 7 or less?

The pivot table is referencing a column that shows the number of days to a task deadline. I'd like it to automatically show only the items with 7 days or less. After some research online, I've found that I can tell the pivot table to exclude certain values - along the lines of:

Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("Days_Due")
    pf.PivotItems("8").Visible = False

But this isn't workable as I can't programme any possible numeric that is not 7 or less to be not visible. Is there a neater way to do this? Thanks.

1
excelpivot-tablevba
You could do something along the lines of this: for i=8 to HighestNumberOfDaysDue pf.PivotItems("i").visible = false Next – Tom K.

1 Answers

1
votes

You can use a label filter:

Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("Days_Due")
pf.PivotFilters.Add Type:=xlCaptionIsLessThan, Value1:="8"