pivot filter based on range

daveyc18

Well-known Member
Joined
Feb 11, 2013
Messages
705
Office Version
  1. 365
  2. 2010
right now i have this...

Code:
  With pf
        For Each pi In pf.PivotItems
        If pi.Name = "811" or pi.name = "911" Then
        pi.Visible = True
        Else
        pi.Visible = False
        End If
        Next pi
    End With

but i'd like this to be more flexible and dynamic. so, rather than hardcode the item "811" and "911"....i'd like the macro to filter the report filter items by looping through a range somewhere in the spreadsheet.

so, let's say in sheet1 column A....I have 811 in A1, 911, in A2, and perhaps more in the future, and I gave it a named range "filter" (which uses an offset formula to make the named range dynamic). So the macro filters the report filter based on the named range "filter."
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try this

Change data in blue by your information.

Code:
Sub Filter_Pivot()
  Dim tb As PivotTable, pf As PivotField, pi As PivotItem
  Dim f As Range, r As Range, r2 As Range, exist As Boolean, c As Range
  Application.ScreenUpdating = False
  '
  Set tb = ActiveSheet.PivotTables("[COLOR=#0000ff]Table1[/COLOR]")
  Set pf = tb.PivotFields("[COLOR=#0000ff]ID[/COLOR]")
  Set r = Range("[COLOR=#0000ff]filter[/COLOR]")    'It can be on the same sheet or on any other sheet of the book.
  With pf
    .ClearAllFilters
    Set r2 = .DataRange
    For Each c In r
      Set f = r2.Find(c, , xlValues, xlWhole)
      If Not f Is Nothing Then
        exist = True
        Exit For
      End If
    Next
    If exist = False Then
      MsgBox "[COLOR=#0000ff]The data to be filtered does not match[/COLOR]"
      Exit Sub
    End If
    For Each pi In .PivotItems
      exist = False
      For Each c In r
        If LCase(pi) = LCase(c) Then
          exist = True
          Exit For
        End If
      Next
      If exist = False Then pi.Visible = False
    Next
  End With
End Sub
 
Upvote 0
Try this

Change data in blue by your information.

Code:
Sub Filter_Pivot()
  Dim tb As PivotTable, pf As PivotField, pi As PivotItem
  Dim f As Range, r As Range, r2 As Range, exist As Boolean, c As Range
  Application.ScreenUpdating = False
  '
  Set tb = ActiveSheet.PivotTables("[COLOR=#0000ff]Table1[/COLOR]")
  Set pf = tb.PivotFields("[COLOR=#0000ff]ID[/COLOR]")
  Set r = Range("[COLOR=#0000ff]filter[/COLOR]")    'It can be on the same sheet or on any other sheet of the book.
  With pf
    .ClearAllFilters
    Set r2 = .DataRange
    For Each c In r
      Set f = r2.Find(c, , xlValues, xlWhole)
      If Not f Is Nothing Then
        exist = True
        Exit For
      End If
    Next
    If exist = False Then
      MsgBox "[COLOR=#0000ff]The data to be filtered does not match[/COLOR]"
      Exit Sub
    End If
    For Each pi In .PivotItems
      exist = False
      For Each c In r
        If LCase(pi) = LCase(c) Then
          exist = True
          Exit For
        End If
      Next
      If exist = False Then pi.Visible = False
    Next
  End With
End Sub

it didn't work initially because my filter is a "report filteR" and not a "row label." however, i simply made it a "row label" isntead and the code works fine. i assume it didn't work initially because "r2" looks at the data range , so the pivotfield has to be a row or column and not a report filter.

thanks a lot.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
actually, how would you modify the code so that it does the opposite: based on that Range("filter"), EXCLUDE those items from the pivot.

Hi @daveyc18, try this:

Code:
Sub Filter_Pivot()
  Dim tb As PivotTable, pf As PivotField, pi As PivotItem
  Dim f As Range, r As Range, r2 As Range, exist As Boolean, c As Range
  Application.ScreenUpdating = False
  '
  Set tb = ActiveSheet.PivotTables("Table1")
  Set pf = tb.PivotFields("ID")
  Set r = Range("filter")    'It can be on the same sheet or on any other sheet of the book.
  With pf
    .ClearAllFilters
    
    For Each pi In .PivotItems
      exist = False
      For Each c In r
        If LCase(pi) = LCase(c) Then
          exist = True
          Exit For
        End If
      Next
      If exist = [COLOR=#ff0000][B]True [/B][/COLOR]Then pi.Visible = False
    Next
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top