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.

the error is as in the title: "unable to set the visible property of the pivotitem class"

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