Unable to set PivotItem.Visible

[ChadElzinga]

I've written a little script to restrict the PivotItems that are visible for a specific PivotField by iterating through the pivot items and setting their visibility to false if they don't fall within a user specified range. The code currently looks something like this...

Application.ScreenUpdating = False
items = Sheet1.PivotTables(1).PivotField("Date&quot.PivotItems
itemCount = items.Count
For counter = 1 To itemCount
currentValue = CDate(loItems(llCounter).Value)
If ("currentValue within range&quot Then
items.Item(counter).Visible = True
Else
items.Item(llCounter).Visible = False
End If
counter = counter + 1
Next
Application.ScreenUpdating = True

This is functional, but I think it's still doing the screen calculations in the background every time I set a PivotItem to visible or hidden. I was wondering if anybody out there knew how I could change a bunch of the PivotItems before it updated the spreadsheet like it does with the PivotField dropdowns?

Thank you,

Chad Elzinga

 

[combo]

Three comments:

1) do you intentionally use two variables: llCounter and counter in the loop?
2) using counter = counter + 1 within the loop you skip some values (Next statement does the same again)
3) carefully check condition ("currentValue within range&quot, add a breakpoint in the next line to see what's going on.

 

[ChadElzinga]

Sorry, I was trying to get rid of some of my naming conventions to simplify the reading of the code. I switched from a While loop to a For loop when I was writing the post because I wasn't familiar with VB sytax when I first wrote the macro. I forgot to take out the line where I increment the counter. I wasn't thorough enough and I apologize. It should look like this...

Application.ScreenUpdating = False
items = Sheet1.PivotTables(1).PivotField("Date&quot.PivotItems
itemCount = items.Count
For counter = 1 To itemCount
currentValue = CDate(loItems(counter).Value)
If ("currentValue within range&quot Then
items.Item(counter).Visible = True
Else
items.Item(counter).Visible = False
End If
Next
Application.ScreenUpdating = True