Using VBA to Filter a Pivot Table - How to add Two Filters

Romela · May 30th 2020

I have some sample code below that is working fine for me. Currently has one filter (on Facility Number) and I want to add another filter.

Code

Private Sub PopulateRentalSpaceUsage()            'get combobox items for "Rental Space Type for selection by user'

     cbo_SelectRentalUsage.Clear
     myfilterFacNum = Sheets("AdminCtrls").Range("B12").Value   'Value 1 Filter
     
     Set PvtTblRental = Worksheets("AdminCtrls").PivotTables("pt_UsageRS")
     myfilterTypePS = Sheets("AdminCtrls").Range("B15").Value

     With PvtTblRental
         .PivotFields("Facility Number").ClearLabelFilters
         .PivotFields("Facility Number").PivotFilters.Add Type:=xlCaptionEquals, Value1:=myfilterFacNum
     End With

     'get all "PS Usage Type" items for the combobox"
     For Each cellRS In Range("RS_ptRange")
         ThisrowRS = cellRS.row
         If Not cellRS.Rows.Hidden And ThisrowRS <> LastrowRS Then
             cbo_SelectRentalUsage.AddItem cellRS.Value
         End If
            LastrowRS = ThisrowRS
     Next cellRS

End Sub

Display More

I am having a problem with this line.

.PivotFields("Facility Number").PivotFilters.Add Type:=xlCaptionEquals, Value1:=myfilterFacNum

Which filters the pt as desired. But how do I add a "second filter" - seems I cannot get the syntax correct. thanks for your suggestions.

**royUK** · May 30th 2020

An example workbook would help.

Romela · May 30th 2020

I thought about an example but since the code is working correctly for me I decided to just show the code.

Since ".PivotFields("Facility Number").PivotFilters.Add Type:=xlCaptionEquals, Value1:=myfilterFacNum" is the line of interest; and more specifically, "Value1:=myfilterFacNum" is the filter being applied to the pivot table.

I am asking how do I insert a "Value2:=myfilter2" using the "PivotFilters" property so the pivot table will now be filtered on Two Criteria.

Thanks for your response.

Using VBA to Filter a Pivot Table - How to add Two Filters

Participate now!

Share

Tags