CODEWITHSUNDEEP
vbaexcelfilterpivot-table
J. L. Muller
J. L. Muller

Reputation: 307

Filter items with certain text in a Pivot Table using VBA

I've been trying to build a code to filter all items within a Pivot Table which contain a specific text fragment. I initially imagined I could use asterisks (*) to indicate any string before or after my text, but VBA reads that as a character instead. This is necessary to display the Pivot Table array in a Userform Listbox. Look what I tried:

Sub FilterCstomers()

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

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

End Sub

Upvotes: 0

Views: 13558

Answers (3)

quollio
quollio

Reputation: 11

Why not just:

.PivotFields("PivotFieldName").PivotFilters.Add2 Type:=xlCaptionContains, Value1:="X"

Upvotes: 1

jeffreyweir
jeffreyweir

Reputation: 4824

You can tweak Shai's answer to significantly speed things up, by:

  1. removing the TRUE branch of the IF as it is not needed
  2. setting ManualUpdate to TRUE while the code executes, to stop the PivotTable from recalculating each time you change the visible status of any PivotItems.

  3. Turning off screen updating and calculation (in case there are volatile functions in the workbook) until you are done

    You probably also want to put an Option CompareText in there if you want your comparisons to be case insensitive.

And you probably want some error handling in case the user types something that doesn't exist in the PivotTable.

You might want to give my blogpost on this stuff a read, because PivotTables are very slow to filter, and it discusses many ways to speed things up

Here's a reworked example of Shai's code:

Option Explicit
Option Compare Text

Sub FilterCstomers()

    Dim pt  As PivotTable
    Dim pf  As PivotField
    Dim pi  As PivotItem
    Dim f   As String

    f = InputBox("Type the text you want to filter:")
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set pt = Sheets("Customers Pivot").PivotTables("Customers_PivotTable")
    Set pf = pt.PivotFields("Concatenation for filtering")
    pt.ManualUpdate = True
    With pf
        .ClearAllFilters
        On Error GoTo ErrHandler
        For Each pi In .PivotItems
            If Not pi.Name Like "*" & f & "*" Then
                pi.Visible = False
            End If
        Next pi

    End With
ErrHandler:
        If Err.Number <> 0 Then pf.ClearAllFilters
        pt.ManualUpdate = False
        On Error GoTo 0
        With Application
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
        End With
    End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

Try the code below to filter all items in field "Concatenation for filtering" that are Like wild card * and String f received from InputBox.

Option Explicit

Sub FilterCstomers()

    Dim PvtTbl      As PivotTable
    Dim PvtItm      As PivotItem
    Dim f           As String

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

    ' set the pivot table
    Set PvtTbl = Sheets("Customers Pivot").PivotTables("Customers_PivotTable")

    With PvtTbl.PivotFields("Concatenation for filtering")
        .ClearAllFilters

        For Each PvtItm In .PivotItems
            If PvtItm.Name Like "*" & f & "*" Then
                PvtItm.Visible = True
            Else
                PvtItm.Visible = False
            End If
        Next PvtItm
    End With

End Sub

Upvotes: 3

Related Questions