Filtering within a pivot table

C

Chris

I have a pivot table that is a ranking based on dollar sales. When I change
to a different geography many products with "0" sales will show up in the
pivot table. I would like to only see products that have dollar sales
greater than "0".

Thanks for any suggestions.

Chris
 
D

Debra Dalgleish

The following macro will hide the row items with zero total, in Excel
2002 or later, where the row field is "Code", and data field is
"Amount". You could adapt this to your pivot table layout.

'==========================
Sub HideZeroRowTotals()
'hide rows that contain zero totals
Dim r As Integer
Dim rTop As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf 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("Amount") 'data field
Set pf = pt.PivotFields("Code") 'row field
rTop = 4 'number of rows before data starts
For Each pi In pf.PivotItems
On Error Resume Next
pi.Visible = True
Next pi
i = pf.PivotItems.Count + rTop
For r = i To rTop - 1 Step -1
On Error Resume Next
str = Cells(r, 1).Value
Set pd = pt.GetPivotData(df.Value, pf.Value, str)
If pd.Value = 0 Then
pf.PivotItems(str).Visible = False
End If
Next r

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top