Excel VBA - Pivot table - Hide calc items when shows 0

  • Thread starter Thread starter edd17
  • Start date Start date
E

edd17

Hi,

I would like to know how to write a macros that would hide a calculate
item when there is "0" as the result of the calculation in th
datafields.

In fact the problem happens anytime I have a field in xlrowfield tha
is not displaying the empty values of the datafield, but when I add
calculated item, then the pivot shows 0 in the data where there was n
data. And I want to hide all these ones.

Tanks that would be a great help for me ...
ed
 
The following code will hide rows where the calculated item has a value
of zero:

'========================================
Sub HideZeroCalcItemRows()
'hide rows that contain zeros for calculated items
'by Debra Dalgleish
Dim r As Range
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim df As PivotField
Dim pi 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 r In pt.DataBodyRange.Rows
On Error Resume Next
str = Cells(r.Row, 1).Value
Set pd = pt.GetPivotData(df.Value, pf1.Value, pi.Value, pf2.Value, str)
If pd.Value = 0 Then
r.EntireRow.Hidden = True
Else
'unhide any previously hidden rows
r.EntireRow.Hidden = False
End If
Next r
End Sub
'==================================
 
Thanks a lot, for the tip,
but as I read the code you are hidding the rows of the spreadsheet an
not hidding the items were the value of the data is 0. Is there a wa
to do that ?
thanks.
Ed
 
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
 
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
'==========================================
 
Back
Top