Please note javascript is required for full website functionality.


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!