I posted this in another forum and got 0 replies so I'm trying my luck here instead...

Is there a way to get rid of an entire row if the condition is met that ALL columns value = 0?. This is in a pivot table whose source data frequently changes. It is impractical to do manually since there are about 20 of these tables in my workbook.


row field.1 column.1 column.2 column.3
A 50% 30% 20%
B 50% 20% 40%
D 0% 0% 0%
P 0% 50% 40%
Total 100% 100% 100%


I want to "visible = FALSE" row "D" without hiding row "P"
My hope is that there is a built-in feature that I don't know about in order to eliminate 0 rows if all values are 0.

If not, I guess a VBA code would do the trick...

if there was a way to get the pivotitem.SUBTOTAL then I could run something like this:

Sub oPI()
Dim oPI As PivotItem
         For Each oPI In Worksheets("Resource MAP").PivotTables("PivotTable1").PivotFields("Nationwide Code").PivotItems
            If oPI.SUBTOTAL = "0" Then
            oPI.Visible = False
            End If
         Next oPI
          
End Sub

otherwise I'll have to go to each calculated value field and make sure they're all 0. Something like this, which is much messier:

with pivot table
     for each field in row field.1 
          if column.1 value = 0 AND column.2 value = 0 AND column.3 value = 0 then
               field.visible=FALSE
     next field
end with
[/QUOTE]