Please Help- How To Retrieve All Pivot Table Filters

[sergioza]

Hi,

I can't know what fields will be filtered on so, so I need to extract all filters at run time. I need some VBA line(s) to do it please.

I tried activeCell.PivotCell.PivotTable.ActiveFilters and this returns 0 although there are definitely filters available.

The workaround which I'm not is very good I'm considering is:

activecell.PivotTable.PageFields.Items collection which also behaves weird:

activecell.PivotTable.PageFields.Count returns 2 at the same time it has 3 elements:
activecell.PivotTable.Item(0);activecell.PivotTable.Item(1);activecell.PivotTable.Item(2)


Many Thanx

 

[Jerry Sullivan]

Playing around with it a little it appears to work with Value fields that are filtered with a criteria. Somewhat like PivotTable filter criteria.

Filtering data values > 200, shows output using the code below.
Looks like it doesn't include Page, Row or Column filters that are checked off.

Just in case anyone finds this page searching on the keyword ActiveFilters, I was able to get ActiveFilters property data for Rowfields when filtering using a criteria, like Starts with "Region A". ActiveFilters doesn't appear to relate to filters that are applied by clicking the checkboxes for individual PivotItems.

 

[sergioza]

Hi Jerry,

1. It's either me or Excel 2010, but ActiveFilters count returns 0 for me

2. It looked to me that PivotFields collections was definitely 0 based.

Regards

S

 

[Jerry Sullivan]

1. It's either me or Excel 2010, but ActiveFilters count returns 0 for me

2. It looked to me that PivotFields collections was definitely 0 based.

Hi sergioza,

Those are two separate issues.
If ActiveFilters.Count returns 0, it means there are no ActiveFilters (filters defined by Criteria) in the PivotTable.

You can confirm that the PivotFields collection is One-Based by trying the code below on a PivotTable with matching names and 3 PivotFields.

Sub Test_PT_Base()
    Dim PT As PivotTable
    Set PT = ActiveSheet.PivotTables(1)
    Dim i As Long
      
    For i = 1 To PT.PivotFields.Count
        Debug.Print PT.PivotFields(i).Name
    Next i
End Sub

The code above will list all 3 fields.
If you change it to:
For i = 0 To PT.PivotFields.Count

You'll get an error, because there is no PT.PivotFields(0)

 

[sergioza]

maybe I'm missing something but

1. I've not been drinking, plan for a glass of wine in 20 minutes but

pt.Pagefields.item(0) definitely returns name of the field as well as
pt.Pagefields.item(1) as well as pt.Pagefields.item(2)

and pt.Pagefields is 2

???

2. What is criteria based filter?

Regards

S

 

[Jerry Sullivan]

maybe I'm missing something but

1. I've not been drinking, plan for a glass of wine in 20 minutes but

...the countdown is over.

pt.Pagefields.item(0) definitely returns name of the field as well as
pt.Pagefields.item(1) as well as pt.Pagefields.item(2)

and pt.Pagefields is 2

???

did you mean Pagefields or PivotFields?
In xl2007 Pagefields doesn't have an Items Property and returns an error.
PivotFields.Item(0) is slightly different than PivotFields(0).

The former will return a value which appears to be the same as PivotFields.Item(1)

So if your fields are City, State, Country
PivotFields.Count returns 3
PivotFields.Item(1) and PivotFields(1) return City
PivotFields.Item(0) also returns City
PivotFields(0) returns an error.

I might be using the terminology incorrectly, but based on that I think of PivotFields as One-based.

2. What is criteria based filter?

Here's an example. Right click on a Row label > Filter... > Value Filters.
A dialog box pops up that allows you to select a datafield, a criterial type "is greater than or equal to" and a value.

This is comparable to what you see in AutoFilter criteria.
When you use a filter like this, it gets added to the ActiveFilters collection (which I hadn't heard of until you asked about it).

 

[sergioza]

Hello Jerry,

after 2 glasses of wine and 10 hours later
:}

1. It looks to me that PivotFields are 1 based and PageFields are 0 based;
is it possible that new collections in Excel 2010 are 0 based?
It used to be this kind of change in VB at some point

2. Thank You for "ActriveFilters" it works as you described.

Also, do you happen to know Microsoft moderated/monitored Excel -SSAS(Pivots) group. I have a few questions related to xlActionType (Excel 2010)enumerations which I can't find any documentation or examples of use.

Happy Holidays

S

 

[elricho17]

I now this is a old post, but i have the same problem, how can I do to get by vb code , the value that the user use in the filter ? Thank you so much