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

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