Hide Zeros in Pivot Tabel
 

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").Piv otItems
If Application.Sum(pvtitm.DataRange) = 0 Then: pvtitm.Visible = False
Next pvtitm
End Sub

Hide Zeros in Pivot Tabel
 

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

Roger wrote:
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").Piv otItems
If Application.Sum(pvtitm.DataRange) = 0 Then: pvtitm.Visible = False
Next pvtitm
End Sub




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hide Zeros in Pivot Tabel
 

On May 7, 9:37 pm, Debra Dalgleish wrote:
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



Roger wrote:
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").Piv otItems
If Application.Sum(pvtitm.DataRange) = 0 Then: pvtitm.Visible = False
Next pvtitm
End Sub

--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html


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

Hide Zeros in Pivot Tabel
 

On May 8, 10:30 am, wrote:
On May 7, 9:37 pm, Debra Dalgleish wrote:



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

Roger wrote:
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").Piv otItems
If Application.Sum(pvtitm.DataRange) = 0 Then: pvtitm.Visible = False
Next pvtitm
End Sub

--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html

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