title: Filter pivot table data field
body: <p>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):</p>
  
  <p><code>Row Labels        Sum of things
   XXXXXXXXX         0
   YYYYYYYYY         0
   ZZZZZZZZZ         2045
   AAAAAAAAA         0
   BBBBBBBBB         0</code></p>
  
  <p>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.</p>
  
  <p>So that would leave me with</p>
  
  <p><code>Row Labels        Sum of things
   ZZZZZZZZZ         2045</code></p>
  
  <p>Once I've got all the elements left, <strong>I'd like to get all these values (row label + sum of things greater than 0) and copy them into another sheet.</strong></p>
  
  <p>Here is what I've done so far:</p>
  
  <p><strong>Creating the Pivot table</strong></p>
  
  <pre><code>  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 " &amp; whatToPivot &amp; " Amount"), "Sum of things", xlSum
  </code></pre>
  
  <p><strong>Performing the filter I wanted: only keep rows having "Sum of things" greater than 0</strong>  </p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>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)</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>What I'd like is only looping through </p>
  
  <pre><code>ZZZZZZZZZ         2045
  </code></pre>
  
  <p>I really can't figure out why these filtered elements are still being considered as visible</p>
  
  <p>Thanks for any help</p>