Hide Zeros in Pivot Tabel

[Roger]

I borrowed this code from a Debra Dalgleish post a while back and have
gotten much use from it. However, I am trying to use it in another macro and
I am not able to get it to do anything. It runs without errors but it does
not actually do anything. My pivot tabel isn't complicated by pivot table
standards. I have one item in the row field, no column items and three data
items. One of the data items is a calcualted field called Diff. It just
subtracts the first data item from the second. I am trying to get the macro
to go down the values in the Diff field and when it finds a zero then it
will hide that pivot item.

Does anyone have any suggestions?

Thanks,

Roger



Sub Hide_zeros()
Dim pvtitm As PivotItem
For Each pvtitm In
ActiveSheet.PivotTables(1).PivotFields("Diff").PivotItems
If Application.Sum(pvtitm.DataRange) = 0 Then: pvtitm.Visible = False
Next pvtitm
End Sub

 

[Debra Dalgleish]

Diff is a calculated field so it doesn't have pivot items to loop
through. You could test the value for each item in the row field, e.g.:

Sub Hide_zeros()
Dim pt As PivotTable
Dim pf As PivotField
Dim pvtitm As PivotItem
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Employee")
Debug.Print pf.PivotItems.Count
For Each pvtitm In pf.PivotItems
If pt.GetPivotData("Diff", pf.Name, pvtitm) = 0 Then
pvtitm.Visible = False
End If
Next pvtitm
End Sub

 

[martinwroger]

Diff is a calculated field so it doesn't have pivot items to loop
through. You could test the value for each item in the row field, e.g.:

Sub Hide_zeros()
Dim pt As PivotTable
Dim pf As PivotField
Dim pvtitm As PivotItem
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Employee")
Debug.Print pf.PivotItems.Count
For Each pvtitm In pf.PivotItems
If pt.GetPivotData("Diff", pf.Name, pvtitm) = 0 Then
pvtitm.Visible = False
End If
Next pvtitm
End Sub

Thanks Debra, I copied your code and changed the "Employee" reference
to match my Pivot Table name and I get a run time error '438'

"Object doesn't support this property or method" on this line

If pt.GetPivotData("Diff", pf.Name, pvtitm) = 0 Then

 

[martinwroger]

Thanks Debra, I copied your code and changed the "Employee" reference
to match my Pivot Table name and I get a run time error '438'

"Object doesn't support this property or method" on this line

If pt.GetPivotData("Diff", pf.Name, pvtitm) = 0 Then

I for got to add that I am using XL2000 SP3