VBA: Filter multiple items of a PivotTable containing certain text

Question

VBA: Filter multiple items of a PivotTable containing certain text

Navigation

#1 by (0 votes)

0

I am trying to run a code that filters all the items in a PivotTable that contain a certain text. I figured that just using asterisks (*) before and after mine keyword would get results, but VBA reads asterisks as literal characters. How do I filter ALL items that have the word written in the InputBox?

Sub FilterCustomers()

    Dim f As String: f = InputBox("Type the text you want to filter:")

    With Sheets("Customers").PivotTables("Customers_PivotTable")
        .ClearAllFilters
        .PivotFields("Concatenation for filtering").CurrentPage = "*f*"
        End With

End Sub

excel vba filtro

asked by anonymous 21.03.2017 / 15:18

1 answer

Error nullpointer when doing query in the bank with parameter Function to dequeue in C

score 0 · Accepted Answer

To filter multiple items in a PivotTable, you should read item by item and mark them as visible or not. And, to compare texts contained in others, you should use the Like statement. So the most appropriate way to write the code for the desired purpose is:

Sub FilterCstomers()

    Dim f As String: f = InputBox("Type the text you want to filter:")

    Dim PvtItm As PivotItem
    With Sheets("Customers Pivot").PivotTables("Customers_PivotTable")
        .ClearAllFilters
        For Each PvtItm In .PivotFields("Concatenation for filtering").PivotItems
            If PvtItm.Name Like "*" & f & "*" Then
                PvtItm.Visible = True
            Else
                PvtItm.Visible = False
                End If
            Next PvtItm
        End With

End Sub