unable to set the visible property of the pivotitem class

[goldfrapp01]

Hi all,

I am using code to filter a pivot table field, setting it to multiple values: "Mexico" and "Canada". All possible locations are "Mexico", "Canada" and "United States".

If my data has both Mexico and Canada, the code works fine, but if the data has no entries for Canada, the code breaks because there are no Canada entries that it can hide.

Any help is appreciated!


Sub FilterAMS()
Dim dblStart As Double: dblStart = Timer
Dim varItemList() As Variant
Dim strItem1 As String
Dim i As Long

Application.ScreenUpdating = False
varItemList = Array("Mexico", "Canada")
strItem1 = varItemList(LBound(varItemList))
With Sheets("comment search").PivotTables("PivotTable1").PivotFields("geography_name")

.PivotItems(strItem1).Visible = True
For i = 1 To .PivotItems.Count
If .PivotItems(i) <> strItem1 And _
.PivotItems(i).Visible = True Then
.PivotItems(i).Visible = False
End If
Next i
For i = LBound(varItemList) + 1 To UBound(varItemList)
.PivotItems(varItemList(i)).Visible = True
Next i
End With
End Sub

 

[par60056]

One problem that often happens is that you end up with no items selected.

Before going into the loop, make everything visible.

For speed, you will also want to set Sheets("comment search").PivotTables("PivotTable1").ManualUpdate = True before the loop and False after the loop.

 

[goldfrapp01]

Thanks! Can you please show how that would look in the code? Even though I understand what needs to happen I don't know how to write it out in code.

The ManualUpdate definitely speeded up the process btw!

 

[par60056]

I started to try and write how I would do it.

You have something called varItemList which appears to be the things you want to see. Is this something that you need to be variable? Will it have a variable number of items in it or always 2? I assume since they are in a variable that they could be different values.

 

[goldfrapp01]

In a nutshell: constant within pivot, so always 2. But then, i might want to re-use the code for another pivot table, where this may be 3 (EMEA, AMS, APJ), which would be then constant at 3. Thanks!

 

[par60056]

If they are hard coded and there are only a few it is much simpler to just hard code the.

I was going to write something that looped over the array checking the values. That is what I would do if there were more than 4 or the array of values was being passed in from somewhere.

See if this does what you want. It will still get an error if there is no data for either.

Sub FilterAMS()
Dim dblStart As Double: dblStart = Timer
Dim varItemList() As Variant
Dim strItem1 As String
Dim i As Long


Application.ScreenUpdating = False
varItemList = Array("Mexico", "Canada")
With Sheets("comment search").PivotTables("PivotTable1")
    .ManualUpdate = True
    With .PivotFields("geography_name")
        .PivotItems("(All)").Visible = True
        For i = 1 To .PivotItems.Count
            If (.PivotItems(i).Name <> "Mexico" And .PivotItems(i).Name <> "Canada") Then
                .PivotItems(i).Visible = False
            End If
        Next
    End With
    .ManualUpdate = False
End With
Application.ScreenUpdating = True
End Sub