Filter pivot table data field

Question

I'm trying to figure out how to properly filter a pivot table based on the calculated sum column (data field) so as for me to be able to gather both label(s) and value(s) left after filtering. Examples being better than words, here is my pivot table (created using the code in below code):

Row Labels Sum of things XXXXXXXXX 0 YYYYYYYYY 0 ZZZZZZZZZ 2045 AAAAAAAAA 0 BBBBBBBBB 0

What I'd like is to filter the pivot table based on the Value field (sum of things), and only keep rows greater than 0.

So that would leave me with

Row Labels Sum of things ZZZZZZZZZ 2045

Once I've got all the elements left, I'd like to get all these values (row label + sum of things greater than 0) and copy them into another sheet.

Here is what I've done so far:

Creating the Pivot table

  workBk.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            workBk.Sheets(workSht.Name).Range("A1").CurrentRegion).CreatePivotTable _
            TableDestination:=workBk.Sheets(workShtPivot.Name).Cells(1, 1), _
            TableName:="PivotTableTest"

        With workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").PivotFields(whatToPivot)
            .Orientation = xlRowField
            .Position = 1
        End With

        workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").AddDataField workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").PivotFields("Net " & whatToPivot & " Amount"), "Sum of things", xlSum

Performing the filter I wanted: only keep rows having "Sum of things" greater than 0

workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").PivotFields(whatToPivot).PivotFilters.Add2 Type:=xlValueIsGreaterThan, _
                                                            DataField:=workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").PivotFields("Sum of things"), Value1:=0

Finally, when trying to loop through the items left (after filtering), it seems like they are all still visible even though they've been filtered out (I'm looping through the entire set of PivotItems, even those equal to 0, which I'd like to avoid)

Dim cellRange As Range
For Each pivotItm In workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").PivotFields(whatToPivot).PivotItems
    If pivotItm.Visible = True Then
        MsgBox (pivotItm.Value)
    End If
Next pivotItm

What I'd like is only looping through

ZZZZZZZZZ         2045

I really can't figure out why these filtered elements are still being considered as visible

Thanks for any help

BigBen BigBen · Accepted Answer · 2018-09-10T21:30:23

I was able to replicate your problem: although the Value Filter is applied, PivotItm.Visible is still True. It seems that the Visible property does not apply to items filtered by Label or Value filters.

If you want to get the visible row label(s) and their corresponding value(s), you can use the DataRange of the PivotFields. For example, something like this to copy the visible row labels and their corresponding values:

Sub FilterPTable()
    Dim pivotTbl As PivotTable
    Dim labelField As PivotField, sumField As PivotField
    Dim copyRng As Range

    Set pivotTbl = ThisWorkbook.Sheets("Sheet1").PivotTables("PivotTable2")
    Set labelField = pivotTbl.PivotFields("Labels")
    Set sumField = pivotTbl.PivotFields("Sum of Things")

    With labelField
        .ClearAllFilters
        .PivotFilters.Add2 Type:=xlValueIsGreaterThan, _
            DataField:=sumField, Value1:=0

        Set copyRng = Union(.DataRange, sumField.DataRange)
        copyRng.Copy ThisWorkbook.Sheets("Sheet2").Range("A1")
    End With
End Sub

Or if your pivot table does not have Grand Totals showing, then you could use the DataBodyRange to refer to the entire range of values within the Pivot Table:

Set copyRng = Union(.DataRange, pivotTbl.DataBodyRange)

Jon Peltier's Referencing Pivot Table Ranges is a useful guide.

N.B. If all the elements are filtered out, this approach will copy the headers, e.g. "Row Labels" and "Sum of Things". You could use Intersect to test if copyRng overlaps the LabelRange of the PivotField to avoid copying in this case.

Filter pivot table data field

1 Answers