using a range with .pivotitems.visible.false

[DPelayo]

I have a specific range of values that I want hidden from view in a pivot table and cannot figure out how to simplify the following formula by using a range/variable for the .pivotitems?

I am trying to simplify the following code:

On Error Resume Next
With ActiveSheet.PivotTables("PivotTable1").PivotFields("LINE")
.PivotItems("510").Visible = False
.PivotItems("515").Visible = False
.PivotItems("520").Visible = False
.PivotItems("525").Visible = False
.PivotItems("530").Visible = False
.PivotItems("535").Visible = False
.PivotItems("540").Visible = False
.PivotItems("545").Visible = False
.PivotItems("550").Visible = False
.PivotItems("555").Visible = False
.PivotItems("560").Visible = False
.PivotItems("565").Visible = False
.PivotItems("570").Visible = False
.PivotItems("575").Visible = False
.PivotItems("580").Visible = False
.PivotItems("585").Visible = False
.PivotItems("590").Visible = False
.PivotItems("595").Visible = False
.PivotItems("520").Visible = False
.PivotItems("615").Visible = False
.PivotItems("640").Visible = False
.PivotItems("645").Visible = False
.PivotItems("650").Visible = False
.PivotItems("655").Visible = False
.PivotItems("660").Visible = False
.PivotItems("665").Visible = False
.PivotItems("670").Visible = False
.PivotItems("675").Visible = False
End With
Next

 

[Marcelo Branco]

Hi,

Some ideas (not tested)

Maybe, if PivotItems of the Field LINE are numeric, you can loop through all PivotItems and test for some conditions. Something like

Dim pvi as PivotItem
 
For each pvi in ActiveSheet.PivotTables("PivotTable1").PivotFields("LINE")
    If pvi.Value >= 500 AND pvi.Value <=575 AND pvi.Value MOD 5 = 0 Then
      pvi.Visible + FALSE
  End If
Next pvi

Note: the Value property of PivotItem returns a string so you can face some problems in comparisons or MOD (not sure)

HTH

M.

 

[Marcelo Branco]

oops...

To loop through all PivotItems i think you need

For each pvi in ActiveSheet.PivotTables("PivotTable1").PivotFields("LINE").PivotItems

(not tested)

M.

 

[DPelayo]

That worked! Thanks

 

[Marcelo Branco]

That worked! Thanks

You are welcome and tks for the feedback

M.