How to Filter a pivot table based on a list? Optimal and efficient - VBA

user_10063762 0
Viewed 265

I'm trying to construct a pivot table and I am filtering the items based on a dynamic list. The list would usually consist of est. 20 items, but the pivot table would have upwards of 5,000 items.

The codes that I have now runs a loop through all 5,000 items and makes those 20 items visible. However, because the dataset is so large (5,000 items), the runtime is extremely long. Is there a better way of running this code to achieve a faster and more efficient result?

I was thinking maybe along the lines of "deselect all" 5,000 items and then find those 20 items and make them visible.

Here is the code that I have now.

Dim PI as PivotItem 
lrow = Main.Cells(Rows.Count, "E").End(xlUp).Row
Set Rng = Main.Range("E1:E" & lrow)

With Main.PivotTables("PivotTable2").PivotFields("Details")
    .ClearAllFilters
    For Each PI In .PivotItems
        PI.Visible = WorksheetFunction.CountIf(Rng, PI.Name) > 0
    Next PI
End With
pivot-table filter vba excel
1 Answers

This was about as fast as I could get. Hiding almost all items for a field with about 5000 unique values takes about 5-6 secs.

Sub Test()
    Dim pt As PivotTable, x As Long, t
    
    Set pt = ActiveSheet.PivotTables(1)
    t = Timer
    pt.ManualUpdate = True                 'tweak #1
    Application.ScreenUpdating = False     'tweak #2
    With pt.PivotFields("Col1")
        .ClearAllFilters
        .AutoSort xlManual, .SourceName    'tweak #3
        For x = 1 To .PivotItems.Count     'tweak #4 - slightly faster than For each
            If x Mod 1000 = 0 Then Debug.Print x
            If Rnd() > 0.007 Then .PivotItems(x).Visible = False 'hiding most of the items...
        Next x
        .AutoSort xlAscending, .SourceName
    End With
    pt.ManualUpdate = False
    Debug.Print Timer - t
End Sub

When applying to your use case I'd use IsError(Application.Match(...)) since that's likely to be faster than running CountIf.

user_4788840
Related