pivot table show items with a value
 

using the code below I have two issues:
1)items with a zero $ value display
2)in the drop-down the selection boxes are not updated
Thanks in advance for your help!

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.RowFields
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
pf.VisibleItems = True
pf.AutoSort xlDescending, pf.Value
Next pf
Next pt

wynnyderpooh

pivot table show items with a value
 

Why don't you record a macro and filter out the zeros. I just did it and got
something like this:

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total")
.PivotItems("$0.00").Visible = False
End With

Obviously, it will be slightly different for you!

To update the Pivot Table, right click somewhere in the area off the table
and click Refresh Data!

For some great information look he
http://www.contextures.com/xlPivot03.html
http://blog.contextures.com/archives...excel-toolbar/

Regards,
Ryan---


--
RyGuy


"wynnyderpooh" wrote:

using the code below I have two issues:
1)items with a zero $ value display
2)in the drop-down the selection boxes are not updated
Thanks in advance for your help!

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.RowFields
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
pf.VisibleItems = True
pf.AutoSort xlDescending, pf.Value
Next pf
Next pt

wynnyderpooh