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

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

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

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

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
'==================================

edd17 < wrote:
Hi,

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

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

Tanks that would be a great help for me ...
edd


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



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

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

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

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

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

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/

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

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