Pivot table automated filter

[frankT68]

Hi!

I'm using this code to filter a pivot table based on ItemId's in named range "Include_this":

Sub Filter_Pivot()

    Dim PI As PivotItem
    Dim i As Integer
 

    With Worksheets("Data_chart").PivotTables("Data_ch1").PivotFields("ItemID")
        .ClearAllFilters 'počisti filtre
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("Include_this"), PI.Name) > 0

        Next PI
    End With
End Sub

So if an ItemID from pivot table is found in named range"Include_this" then it visible in a pivot table, otherwise it is not visible.

Today when I have created a new excel file with a new pivot table and the same "Include_this" range as before, I got an error when running the code. The code runs as usual, but then, when it comes to a certain ItemID, it stops. It shows

Error '1004': Unable to set the Visible property of the PivotItem class​

Debugging shows there is something wrong with this line

PI.Visible = WorksheetFunction.CountIf(Range("Include_this"), PI.Name) > 0

I have no idea what is wrong. As I've said, I'm using this code a lot and haven't had any trouble so far. Does somebody know what could be wrong?

 

[Domenic]

That's probably because your pivot cache is retaining items that you've deleted from the data source. Try setting the MissingItemsLimit property of the PivotCache object to xlMissingItemsNone so that it doesn't retain any old items, and then refresh the pivot cache before proceeding with your code...

Sub Filter_Pivot()

    Dim PI As PivotItem
    Dim i As Integer

    With Worksheets("Data_chart").PivotTables("Data_ch1")
        With .PivotCache
            .MissingItemsLimit = xlMissingItemsNone
            .Refresh
        End With
        With .PivotFields("ItemID")
            .ClearAllFilters 'pocisti filtre
            For Each PI In .PivotItems
                PI.Visible = WorksheetFunction.CountIf(Range("Include_this"), PI.Name) > 0
            Next PI
        End With
    End With
   
End Sub

 

[frankT68]

That's probably because your pivot cache is retaining items that you've deleted from the data source. Try setting the MissingItemsLimit property of the PivotCache object to xlMissingItemsNone so that it doesn't retain any old items, and then refresh the pivot cache before proceeding with your code...

Sub Filter_Pivot()

    Dim PI As PivotItem
    Dim i As Integer

    With Worksheets("Data_chart").PivotTables("Data_ch1")
        With .PivotCache
            .MissingItemsLimit = xlMissingItemsNone
            .Refresh
        End With
        With .PivotFields("ItemID")
            .ClearAllFilters 'pocisti filtre
            For Each PI In .PivotItems
                PI.Visible = WorksheetFunction.CountIf(Range("Include_this"), PI.Name) > 0
            Next PI
        End With
    End With
  
End Sub

Thank you for your advice. After adding the MissingItemsLimit property the code runs just fine.

But I wonder if this problem could also be related to a small amount of RAM? My office computer runs windows 10 with only 4 GB of RAM, which is usually more than 90% occupied.

 

[Domenic]

You're very welcome, glad I could help.

No, I don't think so. With only 4 GB of RAM, though, you most likely find that your computer runs excruciating slow, especially when you have multiple programs opened.