In this post I show the main procedure that filters a pivot table’s source range. In this post, I show how to determine which filters are applied to a particular cell in a pivot table. First, I count the number of filters.
Dim pt As PivotTable
Dim lReturn As Long
Dim ptPage As PivotField
Set pt = Target.PivotTable
lReturn = Target.PivotCell.ColumnItems.Count + Target.PivotCell.RowItems.Count
For Each ptPage In pt.PageFields
If HasFilter(ptPage) Then
lReturn = lReturn + 1
End If
Next ptPage
FilterCount = lReturn
End Function
The PivotCell property returns a PivotCell object for the cell I double clicked. The count of the ColumnItems (the columns that affect the cell), the RowItems (the rows that affect the cell) an any page fields that aren’t “(All)” is the total number of filters I need. I have to count the filters before I actually go get them because there are some cells in pivot tables that aren’t filtered. Notably, the Grand Total cell in the lower, right corner, while part of the pivot table, isn’t actually affected by any filters.
To determine if a page field is filtering the data, I use a separate function HasFilter.
Dim ptItem As PivotItem
Dim breturn As Boolean
breturn = False
For Each ptItem In ptPage.PivotItems
If ptItem.Visible Then
breturn = True
Exit For
End If
Next ptItem
HasFilter = breturn
End Function
If any PivotItem of the page field is visible, then it has a filter.
Finally, my code to store the filters in an array.
Dim aFilters() As String
Dim ptPage As PivotField
Dim ptRowCol As PivotItem
Dim i As Long
Dim pt As PivotTable
Set pt = Target.PivotTable
ReDim aFilters(1 To lFilterCnt, 1 To 2)
lFilterCnt = 0
For Each ptPage In pt.PageFields
If HasFilter(ptPage) Then
lFilterCnt = lFilterCnt + 1
aFilters(lFilterCnt, 1) = ptPage.SourceName
aFilters(lFilterCnt, 2) = ptPage.CurrentPage
End If
Next ptPage
For i = 1 To Target.PivotCell.RowItems.Count
Set ptRowCol = Target.PivotCell.RowItems(i)
lFilterCnt = lFilterCnt + 1
aFilters(lFilterCnt, 1) = ptRowCol.Parent.SourceName
aFilters(lFilterCnt, 2) = ptRowCol.Value
Next i
For i = 1 To Target.PivotCell.ColumnItems.Count
Set ptRowCol = Target.PivotCell.ColumnItems(i)
lFilterCnt = lFilterCnt + 1
aFilters(lFilterCnt, 1) = ptRowCol.Parent.SourceName
aFilters(lFilterCnt, 2) = ptRowCol.Value
Next i
GetFilters = aFilters
End Function
I loop through all the page fields, row items, and column items and fill the array with their SourceName and Value properties (CurrentPage is the Value for page fields).
In my final installment, I’ll show the two calling procedures. One is the DoubleClick event and the other is a menu driven entry point procedure (because I’m a keyboard guy). And if I figure out the grouped date problem, I’ll post that too.
Posting code? Use <pre> tags for VBA and <code> tags for inline.