CODEWITHSUNDEEP
excelfilterpivotvba
user2197263
user2197263

Reputation: 1

Filter Pivot Table for fields containing a value

I am trying to filter the pivottable field "name" to only display records that have "AA5" in the title.

When I record the macro, it seems to only de-select the newest items that do not contain AA5 (using the false property). Rather than using the AA5=true property.

However, since the pivot is constantly updated with new data, running the macro a second time does not remove the newly added items, since the code has identified which NOT to show, rather than which TO show.

Sub Macro3()

    Range("A8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
            With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
        .PivotItems("Test:777:1").Visible = False
        .PivotItems("Test:777:2").Visible = False
        .PivotItems("Test:777:3").Visible = False

    End With

End Sub

Upvotes: 0

Views: 21839

Answers (2)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

It would go this way:

Sub Quick()
    Dim PTfield As PivotField

    Set PTfield = ActiveSheet.PivotTables(1).PivotFields("Name")
    With PTfield
        .ClearAllFilters
        .PivotFilters.Add xlCaptionEquals, , "AA5" 'for exact matching
    End With

End Sub

If AA5 is a part of filtering fields, then change parameter into xlCaptionContains.

Upvotes: 1

Jossy
Jossy

Reputation: 11

Here is a simple macro that actually works. I have a pivot table that contains on Column H Order Fill Rate % of various finished goods. Order Fill Rate of 98.5% or lower is considered no good. This macro will go to Column H starting with Row 5 and go down row by row to check the fill rates. It will hide the rows that are good leaving visible those rows that require futher analysis or action. It will keep doing this until it reaches the "last row"

Sub zSeries09_FilterPivotTable_ByFillRate98pt5() ' ' zSeries09_FilterPivotTable_ByFillRate98pt5 Macro '

' Application.Calculation = xlManual

Dim LR As String

Selection.SpecialCells(xlCellTypeLastCell).Select

LR = ActiveCell.Row

Application.Goto Reference:="R5C8"

Routine:

 If ActiveCell.Row < LR Then GoTo TestError Else GoTo EndCom

TestError:

 If IsError(ActiveCell) = True Then GoTo HideRow Else

TestBlank:

 If IsEmpty(ActiveCell) = True Then GoTo HideRow Else

TestFillRate:

 If ActiveCell.Value >= 0.985 Then GoTo HideRow Else

NoHide:

 Application.Goto Reference:="R[1]C"

 GoTo Routine

HideRow:

 Selection.EntireRow.Hidden = True
Application.Goto Reference:="R[1]C"

GoTo Routine

EndCom:

Application.Calculation = xlAutomatic

End Sub

Upvotes: 1

Related Questions