suppress unwanted rows in a Pivot Table using VBA, dropdowns & Vlo
 

I have a pivot table that when I select the PivotItem field "region" the
table updates to show only those countries in that region fine. But I want
to suppress the countries that are not in the region from even showing. I
have a named range "contries" that contains all the coutries and to the right
its region.

I have tried a couple of ways and currently working the below code using a
independent dropdown. When the user changes the dropdown the below code
executer. The test value always comes back as error 1004. I know this means
it is not finding the value in the "Country" named range, but when I put the
Vlookup formula directly in Excel it works fine.

Sub region_control()
Dim myPick As Long
Dim myDD As DropDown
Dim Answer As String
Dim test As Variant

Set myDD = ActiveSheet.DropDowns(Application.Caller)
Answer = myDD.List(myDD.Value)
For Each ci In ActiveSheet.PivotTables("Destination
Sales").PivotFields("country").PivotItems
test = Application.WorksheetFunction.VLookup(ci.Value, countries, 2,
False)
If IsError(test) Then
ci.Visible = False 'if error suppress row
Else
If test = IPos Then
ci.Visible = True 'show this country
Else
ci.Visible = False 'country not part of region
End If
End If
Next