filter a pivot table with values in an array of cells?

[mrdobbs]

Does anyone know a good way (in Excel 2010) to filter a pivot table to show only certain rows based on the values in an array of cells on another worksheet? <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Say my pivot table has row labels of "SalesRep" and values of "count of Orders", and I want to only see the results for specific (and often arbitrary) SalesRep(s). Is there a way to have Excel filter the pivot table in a way other than unselecting all the values and then hand checking the box next to the SalesRep we want to report on?<o></o>
<o></o>
Thanks (long time reader, first time poster),<o></o>
<o></o>
Matt<o></o>

 

[GarryP]

Hi,

This function has evolved since this Original Post to address exceptions and variations requested.

The latest version be found in this thread.

Pivot table with data validation


The function has this Declaration:

Function Filter_PivotField(pvtField As PivotField, vItems As Variant)

The vItems parameter is a Variant Array of PivotItems whose Visible state you want set to True
You can use a one column wide range reference to make vItems parameter like these examples...

Sub TEST()
    Dim pField as PivotField
    Set pField= Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("MyField")

    Filter_PivotField pvtField:=pField, _
        vItems:=Application.Transpose(Sheets("Sheet2").Range("MyNamedRange"))


    Filter_PivotField pvtField:=pField, _
        vItems:=Application.Transpose(Sheets("Sheet2").Range("C4:C32"))


    Filter_PivotField pvtField:=pField, _
        vItems:=Application.Transpose(Sheets("Sheet2") _
            .Range("D1:D" & Cells(Rows.Count, 4).End(xlUp).Row))

End Sub

If the example you gave returns a valid range, you could use:

    Filter_PivotField pvtField:=pField, _
        vItems:=Application.Transpose(Range(Selection, _
        Selection.Offset(OffsetDown - 1, 0)))

Hi,

Thanks for the great tips. Is there a way to filter OLAP pivot table based on visibility criteria in a regular pivot table? In other words I would like to have the same items in the OLAP-based pivot table as are in the regular pivot table. The principle is the same as discussed in this thread but the target pivot table is different. I think we need to use this somehow (example took from the Microsoft site)


ActiveSheet.PivotTables("PivotTable2").PivotFields("[Customer].[Customer Geography] & _ .[Country]").VisibleItemsList = Array("[Customer].[Customer Geography].[Country].&[Australia]") Excel does not let me use this method: set pvi = .PivotItems(vItems(i)).BR,Kari</pre>

 

[Jerry Sullivan]

Hi Kari,

You could use the Function Store_PT_FilterItems in post #15 to return an array of Visible PivotItems in the regular PivotTable.

This thread might help you with the syntax for building field references for a Pivot using an OLAP data source.
http://www.mrexcel.com/forum/excel-...c-applications-code-update-pivot-filters.html

A good way to start is to manually change some filter selections with the Macro Recorder on, then analyze the resulting macro to see the relationship between the field names in your data source, and the [structured].[references] used by the OLAP sourced pivot.

 

[pedroak]

Hi Matt,

I've seen a few people post variations of this question and thought it might be useful to make a function that could be called using the parameters:

pvtField: The PivotField to be filtered
varItemList: A Variant Array of the items to be Visible
​

Private Function Filter_PivotField(pvtField As PivotField, _
        varItemList As Variant)
    Dim strItem1 As String
    Dim i As Long
    On Error GoTo ErrorHandler:
    Application.ScreenUpdating = False
 
    strItem1 = varItemList(LBound(varItemList))
    With pvtField
        .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
    Exit Function
ErrorHandler:
    MsgBox "Error while trying to process item: " & varItemList(i)
End Function

Below are two examples of how to call the function.

The first uses a hard-coded array for the items.

Sub Filter_ItemListInCode()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("SalesRep"), _
        varItemList:=Array("Adams", "Baker", "Clark")
End Sub

The second example gets the Item list from a named range.

Sub Filter_ItemListInRange()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("SalesRep"), _
        varItemList:=Application.Transpose(Sheets("Sheet2").Range("SalesRepsToShow"))
End Sub

A Dynamic Range is the most flexible if you are going to have a variable number of Sales Reps that you want to display. In order to work with this code the Dynamic Range has to be non-volatile.

Hope this helps!

I was wondering if I could resurrect this post! The code above is great, but wanted to know how you would adjust the script to handle criteria in your array, but that do not occur in this instance of the data for this pivot table? (but may occur in the future). Ie: in the example above "SalesRep" array is a list of names, however at the start of the year one or more of the reps may not have sold anything, however, that does not mean that they won't ever sell anything, so their names still need to be filtered in the list......

That is a bit beyond me yasee!

Huge thanks already for this post....

 

[MurrayBarn]

This is a brilliant piece of code that worked first time for me!

I am still a noob at coding and would like to know how to code an On Error Resume Next into this as I am using the second method to call the function but my SalesRepstoShow range often has items that are not listed in the PivotTable field SalesRep

 

[Ralfdenouden]

Hello Jerry,

I was looking for a code to change the filters in a pivot table with a dynamic named range and tried your code below but I can't get it working. Can you help me out? I'm not so good in VBA and can only inmagine what is happening when reading the code and try all the time if it's working.

The changes I made in the sub for the named ranges are the following:
Sub Filter_ItemListInRange2()
Filter_PivotField _
pvtField:=Sheets("bewerk").PivotTables("Draaitabel1").PivotFields("Bewerkingscode"), _
varItemList:=Application.Transpose(Sheets("Lijsten").Range("Assembly"))
End Sub

It ends up with an error Trying to process item: 403
My pivot table is on the sheet "bewerk", the pivot table is called "Draaitabel1" The field I want to filter is called "Bewerkingscode" and the named range is on the sheet "Lijsten" and is called eg. "Assembly"


I'm working with Excel 2007 and windows 7.

Thank you in advance.
Ralf

Private Function Filter_PivotField(pvtField As PivotField, _
varItemList As Variant)
Dim strItem1 As String
Dim i As Long
On Error GoTo ErrorHandler:
Application.ScreenUpdating = False

strItem1 = varItemList(LBound(varItemList))
With pvtField
.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
Exit Function
ErrorHandler:
MsgBox "Error while trying to process item: " & varItemList(i)
End Function
[/code]

Below are two examples of how to call the function.

The first uses a hard-coded array for the items.

Sub Filter_ItemListInCode()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("SalesRep"), _
        varItemList:=Array("Adams", "Baker", "Clark")
End Sub

The second example gets the Item list from a named range.

Sub Filter_ItemListInRange()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("SalesRep"), _
        varItemList:=Application.Transpose(Sheets("Sheet2").Range("SalesRepsToShow"))
End Sub

A Dynamic Range is the most flexible if you are going to have a variable number of Sales Reps that you want to display. In order to work with this code the Dynamic Range has to be non-volatile.

Hope this helps![/QUOTE]

 

[WesCg]

Hi Jerry,

Thanks for this useful snippet of code, however, I'm running into this issue when i = PivotItems.Count, .PivotItems(i).Visible = False automatically ends my entire subroutine. It doesn't continue at all. Any idea what's going on?

Thanks,
Wes

 

[VETROVJ]

Hello, does this work at the end also with OLAP pivots?

 

[dkmanley]

Jerry, I am trying to do the same thing. I created a separate post today "Filter pivot table field using dynamic range pasted by user".
then i found this thread and tried it. Your code pasted below. I have a dynamic range named "Criteria_Named" and code to select the range is "=OFFSET(Criteria!$D$3,0,0,COUNTA(Criteria!$D:$D)-1,COUNTA(Criteria!$1:$1))" I pasted your code and modified with my sheets and ranges, but when it runs it gets stuck in an endless loop between,

If .PivotItems(i) <> strItem1 And _
.PivotItems(i).Visible = True Then
.PivotItems(i).Visible = False
End If
Next i

Your code modified:

Sub Filter_ItemListInRange()
Filter_PivotField _
pvtField:=Sheets("FCC").PivotTables("PivotTable1").PivotFields("Sku_Pin"), _
varItemList:=Application.Transpose(Sheets("Criteria").Range("Criteria_select"))
End Sub

---

​

Function Filter_PivotField(pvtField As PivotField, _
varItemList As Variant)
Dim strItem1 As String
Dim i As Long
On Error GoTo ErrorHandler:
Application.ScreenUpdating = False

strItem1 = varItemList(LBound(varItemList))
With pvtField
.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
Exit Function
ErrorHandler:
MsgBox "Error while trying to process item: " & varItemList(i)
End Function

Thanks, dkmanley

 

[Pranesh]

Hi Matt,

I've seen a few people post variations of this question and thought it might be useful to make a function that could be called using the parameters:

pvtField: The PivotField to be filtered
varItemList: A Variant Array of the items to be Visible
​

Private Function Filter_PivotField(pvtField As PivotField, _
        varItemList As Variant)
    Dim strItem1 As String
    Dim i As Long
    On Error GoTo ErrorHandler:
    Application.ScreenUpdating = False
 
    strItem1 = varItemList(LBound(varItemList))
    With pvtField
        .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
    Exit Function
ErrorHandler:
    MsgBox "Error while trying to process item: " & varItemList(i)
End Function

Below are two examples of how to call the function.

The first uses a hard-coded array for the items.

Sub Filter_ItemListInCode()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("SalesRep"), _
        varItemList:=Array("Adams", "Baker", "Clark")
End Sub

The second example gets the Item list from a named range.

Sub Filter_ItemListInRange()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("SalesRep"), _
        varItemList:=Application.Transpose(Sheets("Sheet2").Range("SalesRepsToShow"))
End Sub

A Dynamic Range is the most flexible if you are going to have a variable number of Sales Reps that you want to display. In order to work with this code the Dynamic Range has to be non-volatile.

Hope this helps!

Hi Jerry,


I know this an old post. Im looking for the same kind of scenario for my code but unable to get exactly what i need. So thought of asking you help in this post.


In this scenario array is been derived from range of cells and in my scenario im trying to create an array based on a single cell.


I have created a new thread for the same. Please check below link.

https://www.mrexcel.com/forum/excel-questions/1097223-vba-filter-pivot-table.html

Hope you will help me.