Excel VBA to Filter Pivot Table Based on List

VBA Breakdown

Sub PivotTable_Filter_List_1()

• This line defines the start of a subroutine called PivotTable_Filter_List_1.

On Error GoTo Txt

• This line defines an error-handling text that is described later in the code.

 Dim ws As Worksheet
    Dim myArr() As String
    Dim myStr As String
    Dim myPT As PivotTable
    Dim myPF As PivotField

• It declares variables for the worksheet as ws, an array of strings as myArr, a string as myStr, a PivotTable as myPT, and a PivotField as myPF.

Set ws = ActiveSheet
    Set myPT = ws.PivotTables(1)
    Set myPF = myPT.PivotFields("Item")

• It sets the worksheet variable (ws) to the active sheet, the PivotTable variable (myPT) to the first Pivot Table on the worksheet, and the PivotField variable (myPF) to the “Item” field of the Pivot Table.

    myStr = InputBox("Please insert the items in a comma separated way")

• It prompts the user to enter items separated by commas using an InputBox and assigns the input to the string variable (myStr).

    myArr = Split(myStr, ",")

• It splits the string of items into an array using the comma as the delimiter and assigns it to the array variable (myArr).

    myPF.ClearAllFilters

• It clears any previous filters applied to the PivotField.

For i = 1 To myPF.PivotItems.Count
        counter = 0
        For j = LBound(myArr) To UBound(myArr)
            If myArr(j) = myPF.PivotItems(i) Then
                counter = counter + 1
            End If
        Next j
        If counter = 0 Then
            myPF.PivotItems(i).Visible = False
        End If
    Next i

• The nested For loop iterates through each PivotItem in the PivotField and checks if it matches any item in the array. If an item in the PivotField does not match any item in the array, it sets the visibility of that PivotItem to False. That is how it filters out the Pivot Table.

    Exit Sub

• It exits the subroutine.

Txt:
    MsgBox "Not Found"
End Sub

• The code includes error handling with the label “Txt” and displays a MsgBox if an error occurs during the execution of the code. Overall, this code filters a Pivot Table based on an array input.

VBA Breakdown

Sub PivotTable_Filter_List_2()

• This line defines the start of a subroutine called PivotTable_Filter_List_2.

On Error GoTo Txt

• This line defines an error-handling text that is described later in the code.

Dim ws As Worksheet
    Dim myRng As Range
    Dim myPT As PivotTable
    Dim myPF As PivotField

• It declares variables for the worksheet as ws, an range as myRng, a PivotTable as myPT, and a PivotField as myPF.

Set ws = ActiveSheet
    Set myRng = Application.InputBox("Please select the list from the worksheet", Type:=8)
    Set myPT = ws.PivotTables(1)
    Set myPF = myPT.PivotFields("Item")

• The ws variable is set to the currently active worksheet. An InputBox appears, prompting the user to select a range of cells, which is then assigned to the myRng The value of myPT variable is set to the first Pivot Table. The myPF variable is set to the PivotField named “Item” within the Pivot Table.

    myPF.ClearAllFilters

• The previous filters on the PivotField are cleared using the ClearAllFilters method.

For i = 1 To myPF.PivotItems.Count
        counter = 0
        For j = 1 To myRng.Cells.Count
            If myRng.Cells(j) = myPF.PivotItems(i) Then
                counter = counter + 1
            End If
        Next j
        If counter = 0 Then
            myPF.PivotItems(i).Visible = False
        End If
    Next i

• A loop is initiated to iterate through each PivotItem in the PivotField. Within the loop, another loop checks if each item in the selected range matches the current PivotItem. A counter variable keeps track of the number of matches found. If no matches are found (counter = 0), the corresponding PivotItem is set to be invisible (not visible in the Pivot Table).

    Exit Sub

• This command exits the sub. 

Txt:
    MsgBox "Not Found"
End Sub

• The code includes error handling with the label “Txt” and displays a MsgBox if an error occurs during the execution of the code. Overall, this code filters a Pivot Table based on a range input.

Formula Breakdown

COUNTA(Dynamic_Named_Range!$H$5:$H$20)

• This will count the cells that are non-blanks in the range H5:H20.

Result: 3

OFFSET(Dynamic_Named_Range!$H$5,,,COUNTA(Dynamic_Named_Range!$H$5:$H$20)

• Returns the range of cells from the sheet named Dynamic_Named_Range. The number of columns included in the range is not offset from the starting point, resulting in a range with a variable number of rows but no column offset.

Result: {“Apple”;”Banana”;”Mango”}

VBA Breakdown

Sub PivotTable_Filter_List_3()

• This code starts with the subroutine declaration: PivotTable_Filter_List_3.

On Error GoTo Txt

• In the next line, error handling is set up. This means if there is an error during runtime, the program will jump to the Txt label for error handling.

Dim ws As Worksheet
    Dim myRng As Range
    Dim myPT As PivotTable
    Dim myPF As PivotField

• Four variables are declared. Here, ws is a Worksheet Object, myRng is a Range Object, myPT is a PivotTable Object, myPF is a PivotField Object.

   Set ws = ActiveSheet
    Set myPT = ws.PivotTables(1)
    Set myPF = myPT.PivotFields("Item")
    Set myRng = ws.Range("List")

• We set the ws variable to the currently active worksheet, the value of myPT variable to the first Pivot Table and the myPF variable to the PivotField named “Item” within the Pivot Table. myRng variable is set to the dynamic named range (List) of the worksheet ws.

    myPF.ClearAllFilters

• The ClearAllFilters method is called on myPF to remove any previous filters applied to the PivotField.

  For i = 1 To myPF.PivotItems.Count
        counter = 0
        For j = 1 To myRng.Cells.Count
            If myRng.Cells(j) = myPF.PivotItems(i) Then
                counter = counter + 1
            End If
        Next j
        If counter = 0 Then
            myPF.PivotItems(i).Visible = False
        End If
    Next i

• We initiated a For loop to iterate through each PivotItem in the PivotField. Within the loop, another loop checks if each item in the selected range matches the current PivotItem. A counter variable keeps track of the number of matches found. If the loop finds no matches (counter = 0), it will set the corresponding PivotItem invisible (not visible in the Pivot Table).

    Exit Sub

• This line exits the subroutine.

Txt:
    MsgBox "Not Found"        
End Sub

• This is an error-handling message. If an error occurs, a MsgBox appears saying “Not Found”.

VBA Breakdown

Private Sub CommandButton1_Click()

• This line defines the start of an event called CommanButtin1_Click.

On Error GoTo Txt

• This line defines an error-handling text that we will describe later in the code.

Dim ws As Worksheet
    Dim myRng As Range
    Dim myPT As PivotTable
    Dim myPF As PivotField

• We declared four variables. Here, ws is a Worksheet Object, myRng is a Range Object, myPT is a PivotTable Object, myPF is a PivotField Object.

    Set myRng = Range(RefEdit1.Value)

• This line assigns the range specified by the value of the RefEdit1 control (a range input) to the variable myRng.

Set ws = ActiveSheet
    Set myPT = ws.PivotTables(1)
    Set myPF = myPT.PivotFields("Item")

• It sets the worksheet variable (ws) to the active sheet, the PivotTable variable (myPT) to the first Pivot Table on the worksheet, and the PivotField variable (myPF) to the “Item” field of the Pivot Table.

    myPF.ClearAllFilters

• This line will clear the previous filters on the PivotField using the ClearAllFilters method.

For i = 1 To myPF.PivotItems.Count
        counter = 0
        For j = 1 To myRng.Cells.Count
            If myRng.Cells(j) = myPF.PivotItems(i) Then
                counter = counter + 1
            End If
        Next j
        If counter = 0 Then
            myPF.PivotItems(i).Visible = False
        End If
    Next i

• This section of code iterates through each PivotItem in the PivotField. Within the loop, another loop checks if each item in the myRng range matches the current PivotItem. A counter variable keeps track of the number of matches found. If it finds no matches (counter = 0), it will set the corresponding PivotItem to invisible (not visible in the Pivot Table).

    Exit Sub

• This line exits the subroutine.

Txt:
    MsgBox "Not Found"
End Sub

• The code includes error handling with the label “Txt” and displays a message “Not Found” in a MsgBox if an error occurs during the execution of the code.