VBA Blogs: No Filter
21 April 2023
Welcome back to our VBA blog series. It’s been a long time! In this blog, we look at how to remove the filter dropdowns from PivotTables.
Consider the following example of a very simple dashboard:
Having created slicers to manipulate the data in the PivotTable, we would like to remove the filtering from Store and Salesperson. We could do this by unchecking the ‘Field Headers’ in the ‘Show’ section of the ‘PivotTable Analyze’ tab:
However, this also removes the headings:
We would like to remove the filter dropdowns without removing the headings. We can do this with VBA. In the VBA Editor, we enter the following code:
The code is:
Sub DisableFilterArrows()
Dim pt As PivotTable
Dim pf As PivotField
Dim i As Integer
On Error Resume Next
For i = 1 To 100
Set pt = ActiveSheet.PivotTables(i)
For Each pf In pt.PivotFields
pf.EnableItemSelection = False
Next pf
Next i
End Sub
This subroutine runs a loop (currently set to run 100 times) which checks each PivotField on each PivotTable in the currently selected sheet and sets the property EnableItemSelection to ‘False’. To enable the filters, we would set EnableItemSelection to ‘True’. Having run the subroutine to switch off the filters, we can check the PivotTable.
The filters have been removed, and the users will use the slicers to filter the data.
See you next time for more VBA tips!
