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.PivotItems
     If filterValue.Name = selectValue Then
          filterValue.Visible = True
     Else 
          filterValue.Visible = False
     End If
Next 

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.PivotItems
          If filterValue.Name = selectValue Then
               filterValue.Visible = True
          Else 
               filterValue.Visible = False
          End If
     Next 
End If

Lesson learned:

Make sure there is always at least one pivot filter value visible if you don’t want to get run-time errors!

Follow

Related posts: