Pivot Table Label Filter Is Applied But Data Does Not Actually Filter

Question

I have a pivot table to which I am attempting to add a label filter to (on a single column) via VBA.

My current code is:

Sub Filter__Pivot()

Application.ScreenUpdating = False

 Worksheets("Summary").PivotTables("Pivot1").PivotFields("Sales"). _
    ClearAllFilters

 Worksheets("Summary").PivotTables("Pivot1").PivotSelect _
    "Sales[All]", xlLabelOnly, True


 Worksheets("Summary").PivotTables("Pivot1").PivotFields("Sales"). _
    PivotFilters.Add2 Type:=xlCaptionIsNotBetween, Value1:="-10", Value2:= _
    "10"

Application.ScreenUpdating = True

End Sub

The code runs without error and the pivot has the appearance of the filter being applied. By this I mean there is the typical filter icon on the column of the pivot I desired. Clicking on this header icon shows that a label filter has been applied and when I click to the filter itself: Filter in header --> Label Filters --> Not Between ......I see the -10 and 10 in the value fields. The issue is that the actual data in the pivot table does not reflect the filter - it has not been applied (ie. Values between -10 and 10 are still visible).

I tried macro recording and my syntax matches. I also tried using Worksheets("Summary").PivotTables("Pivot1").PivotCache.Refresh after the filter is placed but this did not help.

The odd bit, is that if I apply the filter via VBA and then click through as described above, when I click "Okay" at the point I see the -10 and 10 on the Not Between filter, the filter then activates and the data in the pivot reflects the filter.

In essence, my filter is being placed onto the pivot table, but it is not "actioning" and the pivot table data does not reflect the filter that appears to be active on it.

Thanks all!

UPDATE:

I have created the code below:

 With Worksheets("Summary").PivotTables("Pivot1").PivotFields("Sales")
    For i = 1 To .PivotItems.count
        If i > -10 And i < 10 Then
            .PivotItems(i).Visible = False
        Else
            .PivotItems(i).Visible = True
        End If
    Next i
End With

This is not evaluating and filtering based on the values in the Sales Column of my pivot, but it does actually move / filter the data within the table. Is there a way to perhaps adapt this methodology and loop or work through each pivot line and change the .Visibility based on the value in the Sales Column?

This would be a workaround to using filters entirely, but might achieve what I need.

Thanks!

Strange that the pivot cache update didn't solve this. Out of curiosity, does adding ThisWorkbook.RefreshAll to the end of your macro have any effect? — ChrisB
@ChrisB I tried this - it resulted in the same "applied filter" but no actual data filtering within the table. — RugsKid

RugsKid RugsKid · Accepted Answer · 2019-10-07T17:35:17

I was never able to implement a solution that worked by addressing the Pivot Table or Label Filters as originally intended. My workaround was to avoid the pivot table itself and hide rows based on the criteria I outlined in my original questions (between -10 and 10 to be hidden). This may not work for others, and is certainly not the cleanest solution, but for my needs, the code below works:

Sub Filter_Summary_Pivot()
Application.ScreenUpdating = False

    Worksheets("Summary").PivotTables("Pivot1").PivotFields("Sales"). _
    ClearAllFilters

 Worksheets("Summary").PivotTables("Pivot1").PivotSelect _
    "Sales[All]", xlLabelOnly, True

'workaround to pivot filters not actioning data - hides rows based on "isbetween" logic of values in Sales Column

Dim lastrow As Long
lastrow = Worksheets("Summary").Range("C" & Rows.Count).End(xlUp).Row

BeginRow = 5
  EndRow = lastrow
  ChkCol = 6

For RowCnt = BeginRow To EndRow
    If Worksheets("Summary").Cells(RowCnt, ChkCol).Value > -10 And Worksheets("Summary").Cells(RowCnt, ChkCol).Value < 10 Then
        Worksheets("Summary").Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    End If
Next RowCnt

'secondary "filter" to remove "(Blank)" values in Sales column of pivot

BeginRow = 5
  EndRow = lastrow
  ChkCol = 6

For RowCnt = BeginRow To EndRow
    If Worksheets("Summary").Cells(RowCnt, ChkCol).Value = "(blank)" Then
        Worksheets("Summary").Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    End If
Next RowCnt


Application.ScreenUpdating = True

End Sub

Cheers!

Pivot Table Label Filter Is Applied But Data Does Not Actually Filter

2 Answers