Selecting multiple items in excel pivot filter. But I don't want to be specific while mentioning the items in code. Is there any better way than this?

Viewed 45

Sub CreatePivot() Dim PItem as PivotItem Dim PTable as PivotTable

    For Each PItem in PTable.PivotFields("Employee_Name").PivotItems
            If PItem.Name Like "*XXX*" Or PItem.Name Like "*YYY*" Or PItem.Name Like "*ZZZ*"
                    PItem.Visible = True
            Else
                    PItem.Visible = False
            End If
    Next PItem

End Sub

1 Answers

Pivot Table Sub

  • The following might give you some ideas (not tested).
  • Adjust the values in the constants section.
Option Explicit

Sub createPivotTEST()
    
    Const wsName As String = "Sheet1"
    Const ptName As String = "Pivottable1"
    Const pfName As String = "Employee_Name"
    Const CriteriaList As String = "XXX,YYY,ZZZ"

    Dim Criteria() As String: Criteria = Split(CriteriaList, ",")
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim pTable As PivotTable: Set pTable = ws.PivotTables(ptName)
    Dim pField As PivotField: Set pField = pTable.PivotFields(pfName)

    CreatePivot pField, Criteria

End Sub

Sub CreatePivot(ByVal pField As PivotField, ByVal Criteria As Variant)
    
    Dim cLower As Long: cLower = LBound(Criteria)
    Dim cUpper As Long: cUpper = UBound(Criteria)
    
    Dim pItem As PivotItem
    Dim n As Long
    
    For Each pItem In pField.PivotItems
        For n = 0 To cUpper
            If pItem.Name Like "*" & Criteria(n) & "*" Then
                If Not pItem.Visible Then
                    pItem.Visible = True
                End If
                Exit For
            End If
        Next n
        If n > cUpper Then
            If pItem.Visible Then
                pItem.Visible = False
            End If
        End If
    Next pItem

End Sub
Related