VBA Hide Pivot entry featuring text substring as part of string

[kalikj]

Please can anyone help? I would like to hide PIVOT row entries featuring a specific substring as part of the text string for that entry.

By using the following code, I can specify the name of a full entry to be hidden:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("SPECIFIED FIELD")        .PivotItems("PRIVATE LABEL").Visible = False            End With

I would like to hide any entry featuring the following substring (which always starts from position 1): "UK_CORP_MASK_".

I have tried using the InStr function in place of the specified name, but this crashes the code.

Please can anyone suggest a solution.

 

[ParamRay]

like this?

Sub FilterPivot()
  Dim sht As Worksheet
  Dim pvt As PivotTable
  Dim pvf As PivotField
  Dim pvi As PivotItem
  Dim txt As String
  
  Set sht = ThisWorkbook.Sheets("Sheet1")
  Set pvt = sht.PivotTables("PivotTable1")
  Set pvf = pvt.PivotFields("Field1")
  txt = "UK_CORP_MASK"
  
  For Each pvi In pvf.PivotItems
    pvi.Visible = Not InStr(1, pvi.Name, txt, vbTextCompare)
  Next pvi
End Sub

 

[kalikj]

Yes, perfect!

Thank you ParamRay.

The only downside, which is unavoidable, is where one is working with a large number of Pivot Items, as it takes a considerable length of time to cycle through them.