CODEWITHSUNDEEP
vbaexcel
Christian
Christian

Reputation: 25

VBA Pivot Table - How to select multiple criteria in filter?

I'm having trouble getting my pivot table to select multiple values for the B3 filter. When I enter the criteria as an array, it recognizes it as null - "Cannot enter a null value as an item or field name in a PivotTable report." Is there a straightforward way to do this? I couldn't find a solution from Google.

Sub Button5_Click()


Dim docworksheet As Worksheet
Dim docworkbook As Workbook

Set docworkbook = ThisWorkbook
Set docworksheet = docworkbook.Sheets("Analysis")



docworksheet.Activate
ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False



ActiveSheet.Range("B4").Value = "(blank)"
ActiveSheet.Range("B5").Value = "(All)"
ActiveSheet.Range("B2").Value = "(All)"
ActiveSheet.Range("B3").Value = Array("A", "B", "C")
ActiveSheet.Range("B7").Value = "L"

End Sub

Upvotes: 0

Views: 6525

Answers (1)

Chronocidal
Chronocidal

Reputation: 8081

Go hit "Record Macro", then carry out your action, then hit "Stop Recording".

You should have something like this:

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Field Name Here")
    .PivotItems("First Item Name").Visible = True
    .PivotItems("Second Item Name").Visible = False
    .PivotItems("Third Item Name").Visible = True
End With

This goes through and sets each item one-by-one.

So loop through all of the PivotItems in your PivotField, and compare to the values in your Array (e.g. with the Filter function) - for something like this:

Public Sub FilterPivotByArray(Target As PivotField, Values() As String)
    Dim piTMP As PivotItem, bManualUpdate As Boolean

    With Target.Parent
        bManualUpdate = .ManualUpdate 'Store setting for later
        .ManualUpdate = True 'Turn on Manual Update to speed things up
    End With

    Target.ClearAllFilters
    For Each piTMP In Target.PivotItems
        'Visible if Value is in Array
        'Not Visible if Value is Not in Array
        piTMP.Visible = (UBound(Filter(Values, piTMP.Value)) >= 0)
    Next piTMP

    With Target.Parent
        .Update
        .ManualUpdate = bManualUpdate 'restore setting
    End With
End Sub

Upvotes: 3

Related Questions