filter & pivot table
 

Hi,
Can i get help!!
I have a pivot table, and in the data i have a sum of
quantity of items, i dont want to see items that there
sum is zero, (the zero is because of number of records
that there sum is zero!!!).
Can u help me to hide these items?
Thank you,
Suhair

In your pivot table, right click on the field name, choose field options,
clear the check box for "Show items with no data".

Carlos

"suhair" wrote in message
...
Hi,
Can i get help!!
I have a pivot table, and in the data i have a sum of
quantity of items, i dont want to see items that there
sum is zero, (the zero is because of number of records
that there sum is zero!!!).
Can u help me to hide these items?
Thank you,
Suhair

Hi Carlos,
This option is sutable for items that has no sum, and
dont work if you have more than record that there sum is
zero!!!!!
Do you have anther idea??
Suhair

-----Original Message-----
In your pivot table, right click on the field name,
choose field options,
clear the check box for "Show items with no data".

Carlos

"suhair" wrote in
message
...
Hi,
Can i get help!!
I have a pivot table, and in the data i have a sum of
quantity of items, i dont want to see items that there
sum is zero, (the zero is because of number of records
that there sum is zero!!!).
Can u help me to hide these items?
Thank you,
Suhair


.

You could use a macro to hide the rows. For example:

'=============================
Sub HidePivotZeroRows()
'hide worksheet rows that contain all zeros
Dim rng As Range
For Each rng In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
If Application.Sum(rng) = 0 Then
rng.EntireRow.Hidden = True
Else
'unhide any previously hidden rows
rng.EntireRow.Hidden = False
End If
Next rng
End Sub
'=================================

Or for Excel 2002 or later:

'==========================
Sub HideZeroRowTotals()
'hide rows that contain zero totals
'by Debra Dalgleish
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("Units") 'data field
Set pf = pt.PivotFields("Rep") 'column 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
'===================================


suhair wrote:
Hi,
Can i get help!!
I have a pivot table, and in the data i have a sum of
quantity of items, i dont want to see items that there
sum is zero, (the zero is because of number of records
that there sum is zero!!!).
Can u help me to hide these items?
Thank you,
Suhair


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