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

Share

Tags