Filter Pivot Table Source Data II

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.

Public Function FilterCount(Target As Range) As Long
   
    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.

Public Function HasFilter(ptPage As PivotField) As Boolean
   
    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.

Public Function GetFilters(Target As Range, lFilterCnt As Long) As Variant
       
    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.

Posted in Uncategorized


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.