I was working on some VBA code to hide all the values for a particular report filter, except for a specific one. The code looped through all the pivot items for a particular field, turned them off and then turned the one I wanted back on.
Sub SetFilter(ByVal Worksheet As String, ByVal pivotTable As String, ByVal pivotField As String, ByVal selectValue As String) Sheets(Worksheet).Select Dim pvtTable As pivotTable Set pvtTable = ActiveSheet.PivotTables(pivotTable) Dim pvtField As pivotField Set pvtField = pvtTable.PivotFields(pivotField) For Each filterValue In pvtField.PivotItems filterValue.Visible = False Next pvtField.PivotItems(selectValue).Visible = True End Sub
While testing the code, it worked until it hit the last filter value, at which point it would throw an exception:
Run-time error ‘1004’: Application-defined or object-defined error
I kept going around in circles, trying to figure out if Excel has some built-in flaw with For Each loops that caused it to crash on the last element. But soon I stumbled across a Stack Overflow post which kindly pointed out that at least one PivotItem must be visible at all times.
I modified my For Each loop so that it would only turn off the filter if it didn’t match the value I wanted to keep visible.
For Each filterValue In pvtField.PivotItemsIf filterValue.Name = selectValue Then filterValue.Visible = True Else filterValue.Visible = False End If
Update: I had completely neglected the case where there was only one Pivot filter item available. If there was only one value in the filter list and if it didn’t match the particular value I wanted to keep visible, I ran into the same problem. I had to modify my code so that it would only try to hide filter values if there was more than one filter value available:
If pvtField.PivotItems.Count > 1 Then For Each filterValue In pvtField.PivotItemsIf filterValue.Name = selectValue Then filterValue.Visible = True Else filterValue.Visible = False End If Next End If