Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide items with no data in pivot table | Excel Discussion (Misc queries) | |||
Pivot Table: How do I hide calculated items that result in zero? | Charts and Charting in Excel | |||
Calc on Pivot Table | Excel Worksheet Functions | |||
Pivot Table: Hide Items Poperty | Excel Programming | |||
Auto Hide Pivot Table Items | Excel Programming |