CODEWITHSUNDEEP
vbaexcel
Kenny T.
Kenny T.

Reputation: 43

Pivot table - Filter out any number less than a value

On my pivot table with all items selected, I need to deselect every value less than 10. This is what I got as of now but if one of the values defined are not available, this vba will not work.

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count")
.PivotItems("10").Visible = False
.PivotItems("9").Visible = False
.PivotItems("8").Visible = False
.PivotItems("7").Visible = False
.PivotItems("6").Visible = False
.PivotItems("5").Visible = False
.PivotItems("4").Visible = False
End With

Ideally i want something like this:

= .PivotItem("<10").Visible = False

I have seen someone on the forum do a For Next loop shown below, however it was not correct. The loop was counting up to the number of values to hide rather than the numbers it needs to hide.

`On Error Resume Next
For j = 0 To 10 Step 1
    .PivotItems(j).Visible = False
Next j
On Error GoTo 0

For example: I have values number 4 to 10, like the example above. If j = 5:

This loop filters out the 5th value, which is the number 8 (not what I want)

.PivotItems(5).Visible = False

While the code below is filtering out the value 5 (which is what I want). The quotes makes a big difference.

.PivotItems("5").Visible = False

I'm revisiting a problem that still has no correct solutions. Can someone please help?

Pivot table - deselect less than a value

Upvotes: 0

Views: 5731

Answers (2)

Danny Scofield
Danny Scofield

Reputation: 3

Important Issue: Filter section of Pivot Table must be active if you want to run PivotItem.Visible = False

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27249

How about this:

Dim pt as PivotTable
Dim pf as PivotField
Dim pi as PivotItem

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("Count")

For each pi in pf.PivotItems
   If pi.Value < 10 Then pi.Visible = False
Next

Upvotes: 1

Related Questions