Filter Pivot Table Source Data

I started by showing you my awesome code to parse the SourceData of a pivot table. Code that has been rendered obsolete by John’s comment. I was going to continue with the supporting utilities used by the main procedure, but it may be hard to put things into context, so I’ll show the main procedure instead.

Public Sub FilterPivotSource(Target As Range, pt As PivotTable)
    Dim rSource As Range
    Dim vaFilters As Variant
    Dim i As Long
    Dim rFound As Range
    Dim lField As Long
    Dim lOldCalc As Long
    Dim lFilterCnt As Long
    Set rSource = Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))
    rSource.Parent.AutoFilterMode = False
    lFilterCnt = FilterCount(Target)
    If lFilterCnt > 0 Then
        vaFilters = GetFilters(Target, lFilterCnt)
        lOldCalc = Application.Calculation
        Application.Calculation = xlCalculationManual
        With rSource
            For i = LBound(vaFilters, 1) To UBound(vaFilters, 1)
                Set rFound = rSource.Rows(1).Find(vaFilters(i, 1), , xlValues, xlWhole)
                If Not rFound Is Nothing Then
                    lField = rFound.Column – rSource.Cells(1).Column + 1
                    .AutoFilter lField, vaFilters(i, 2)
                End If
            Next i
        End With
        Application.Calculation = lOldCalc
    End If
End Sub

I pass in two variables, Target and pt. Target is a variable in a DoubleClick event – the cell that was double clicked. The pt variable is the pivot table that contains that cell. I do all the testing for whether the cell is actually in a pivot table in the calling procedure, which I’ll show later. I could pass the Target and derive the pivot table via Set pt = Target.PivotTable, but since I already do it in the calling procedure, I don’t do it again.

The main idea of this code is to determine all the filters that are placed on the specific cell in the pivot table, then to create an autofilter on the source data with the same filters. For most cells in a pivot table, the filters applied can come from three places: The row it’s in, the column it’s in, and the page. Let’s look at an example.

Data like this

might produce a pivot table like this

The cell that intersects Gray and North is affected by two filters, Rep and Region. My vaFilters Variant Array holds the filters that I will use to create the autofilter. It looks like this

I’ll show the code that counts the filters and fills the array in a later post. Once I have the filters, I set calculation to manual. That cut the code processing time from almost three seconds to less than 1/2 second.

With the source range, I apply an AutoFilter. Then I loop through all my filters and find the column to which that filter relates. Assuming I found it, I set the AutoFilter constraint using the second value in my array. For instance, I search for the column in my source range with “Rep” in the first row. I compute where in the source range it is and store that in lField. Then I set the autofilter to “Gray”. On the second loop, I do the same thing for “Region” and “North”. With the data properly filtered, I activate the sheet that contains the data.

Currently this only works for Excel 2003. Excel 2007 introduces some wrinkles into the process that I haven’t attempted to iron out yet. There is also this pesky little problem of grouped dates. If my pivot table looks like this instead

the grouped dates don’t play so nice. I’m working on that, but it’s not going well.

In my next post, I’ll show how I compute the number of filters and fill the array.

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.