The GetPivotData method was added in Excel 2002. What version are you using?

The following code will hide the items, instead of the rows:
'===============================================
Sub HideZeroCalcItems()
'hide rows that contain zeros for calculated items
'by Debra Dalgleish
Dim r As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pi2 As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set df = pt.PivotFields("Units") 'data field
Set pf1 = pt.PivotFields("Year") 'column field
Set pf2 = pt.PivotFields("Rep") 'row field
Set pi = pf1.PivotItems("YearVar") 'calculated item

For Each pi2 In pf2.PivotItems
pi2.Visible = True
Next pi2

i = pf2.PivotItems.Count
For r = i To 1 Step -1
On Error Resume Next
str = Cells(r + 5, 1).Value
Set pd = pt.GetPivotData(df.Value, pf1.Value, _
pi.Value, pf2.Value, str)
If pd.Value = 0 Then
pf2.PivotItems(str).Visible = False
End If
Next r

End Sub
'==========================================

edd17 < wrote:
Actually am trying to make this code work but the followwing is not
working .... It always return me 'nothing'.


Set pd = pt.GetPivotData(df.Value, pf1.Value, pi.Value, pf2.Value,
str)

any ideas ?
by the way is there no possibility to directly hide an item instead of
hidding the row ?
thanks a lot ...
Edd


edd17 wrote:

*Thanks a lot, for the tip,
but as I read the code you are hidding the rows of the spreadsheet
and not hidding the items were the value of the data is 0. Is there a
waz to do that ?
thanks.
Edd *



---
Message posted from http://www.ExcelForum.com/



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html