0

I am wondering if it is possible to exclude certain pivot items in a filter? For example I have a set of customers in the row columns of the pivot table, then a filter with their country of operation. I would then like to select a set of countries, say for example country A and C, and then if a customer operates in country A, B and C that customer would be left out of the results.

Would this be possible?

Filip Schöllin
  • 1
  • 1
  • It is most certainly viable, not likely though that you'll be able to do it without Excel VBA. Please do search for existing solutions, if the search is unsuccessful feel free to return with whatever you have tried and add the related tags, e.g. `VBA`. – user3819867 Jul 10 '15 at 11:10
  • Have you tried the built in filters on the row labels/values? If so, can you explain are they are not adequate? Some sample data or a picture would be helpful also. Since you don't have the rep, you can add a link to a picture and I will [edit] it in. – Byron Wall Jul 10 '15 at 15:57
  • Well I tried using the row label filter, but it only hides the countries/regions that are not in the filter. Here's a link to a picture of the pivot table: http://picpaste.com/0-2SKKjszc.jpeg WE, CE, NEE and SE are the things I want to be able to filter on. – Filip Schöllin Jul 13 '15 at 11:59

1 Answers1

0
Sub pivot_table_making_and_filtering()

'----The above code worked for me-- pivottable2 is an existing pivot.....'

Dim pt As PivotTable
Dim pi As PivotItem
Dim first As String
Dim second As String


 Sheets("Sheet1").Select

 first = Range("GTR1").Value
 second = Range("GTR2").Value
 third = Range("GTR3").Value
 fourth = Range("GTR4").Value

 Set pt = Worksheets("Sheet2").PivotTables("PivotTable2")

 With pt.PivotFields("FilterName")
    For Each pi In pt.PivotFields("FilterName").PivotItems
    If pi.Name = first Or pi.Name = second Or pi.Name = third Or pi.Name = fourth Then
        pi.Visible = False
   Else
            pi.Visible = True
        End If
    Next pi
End With

End Sub

Malik Uzair
  • 1
  • 1
    Thank you for this code snippet, which might provide some limited, immediate help. A brief explanation of *how* or *why* your code solves the problem would greatly improve its long-term value and make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you’ve made. – zephryl Feb 14 '22 at 22:32