• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Filter Pivot Table

R

rjacmuto32

Member
I have a pivot table with some filters at the top. I would like to use VBA to filter the pivot table by the word in cell A1. I can do this, my only issue is I would like to Filter by Contains in the word A1. Example is the items in the pivot table fiedl are:

Andrew, Lori
Andrew, Tim, Lori
Lori, John

So if cell a1 equals Andrew I want to filter the pivot table for the lines like ANdrew. In this case the first 2 items.

The code I'm using so far is this. It works if the names only have Andrew in it but since they contain multiple it's not working. I'm not sure how to modify the last part below for current page nrcCriteria to be a contains not exact match

Code: 
Dim nrcCriteria As String
  
    Sheets("RM exclude reasons").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Name Indicator"). _
      ClearAllFilters
 
nrcCriteria = Range("A1").Value
 
If nrcCriteria = "All" Then
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "Name Indicator").ClearAllFilters
Else
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "Name Indicator").CurrentPage = nrcCriteria
End If
      
      
End Sub
 
Hi ,

Can you try replacing your existing statement by this ?

nrcCriteria = "*" & Range("A1").Value & "*"

This uses the wild-card character "*" to mean that if the contents of A1 are anywhere in the field , preceded by any text and followed by any text.

Narayan
 
That one didn't work. Could it have to do with my filter being in the Report Filter of the pivot table? When i normally click the filter at the top of the pivot table i can only select one or multiple items from the drop down. I can't use the Contains or Greater then option like you can in the Row Labels filters in a pivot table.
 
.. No, not rule.. But personal and humble request..
In my Office.. I am also victim of this issue..
 
You must log in or register to reply here.
Back
Top