VBA Pivot Table Filter .ClearAllFilters without replacing data below

Question

I have a code to filter a pivot table to a range of dates no larger than 31 days. Since I know the length of the pivot table I have other data and information below the pivot table to work with. Problem is the data source has a much larger range of dates so when I use the .ClearAllFilters it wants to replace all of the data below before it filters to the correct range.

Here is my simple code:

Set pt = RepSheet.PivotTables("PivotTable1")
    pt.PivotFields("Date").ClearAllFilters
    pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=SelectedDate1, Value2:=SelectedDate2

I have tried the code below to set the filters all to False instead of clearing the filters but I just get an error when trying to set the filters.

With pt.PivotFields("Date")
    For i = 1 To .PivotItems.Count - 1
        .PivotItems(.PivotItems(i).Name).Visible = False
    Next i
End With

Any suggestions? I really don't want to put the data on top of the pivot table.

The reason you are getting an error when you try to remove the manual filter items one at a time is that you cannot have the filter set to show no items at all. — Greg Viers
I added this line .PivotItems(SelectedDate1).Visible = True in the With statement but still get same error, it is selected in the list but not appearing in the table, do I need to refresh? @GregViers — Senor Penguin
I've had challenges like this before, where I had a token item that I always turned on and then turned all others off as a way of setting filters. — Greg Viers
Another totally different approach is to create a "pivot table ditch". Basically, below the table you insert 1000s of empty rows and then hide them. That way when you ClearAllFilters, it doesn't overwrite your data below, it just flows into the ditch. — Greg Viers
For pivot tables, I typically have the some criteria to compare to, and within that if statement i have a variable like a switch, such that z = 0 unless somethign has met criteria and z = 1... so when you get to the last item if i = pivotitems.count and z = 0 then .visible = true. This ensure that at least 1 filter is applied to the data, even if the previous last filter was off. — Cyril

fn_ fn_ · Accepted Answer · 2020-06-18T08:11:56

I figured out a reasonably straightforward workaround to this:

Add a column to your base table which will be used as a dummy filter, e.g. "ClearAllFiltersWorkaround"
Fill it such that it contains the same value for only one of the rows in your pivot table. I used the formula =IF(A1=$A$1,"Single Item Flag",""), where the rows of my pt were filled with items from column A
Edit your pt data source (PivotTable Analyze --> Change Data Source) and add this new field as a filter in your pivot field list
Then, when updating the pivot table, first filter for one row label
Change/Add/Remove pivot fields, sort/filter values or whatever you need to do
Clear your dummy filter

    pt.PivotFields("ClearAllFiltersWorkaround").CurrentPage = "Single Item Flag"
    'Filter/Sort fields, add columns, add values
    pt.PivotFields("ClearAllFiltersWorkaround").ClearAllFilters

VBA Pivot Table Filter .ClearAllFilters without replacing data below

1 Answers