pivot table with 'like' operator

[bryanrobson]

Hi. I have the below code which works well. I just need to amend it, so it shows anything with Q in it so *Q*. I tried changing the Is to a Like and also tried changing the "Q" to "*Q*" which it doesn't like.

Many thanks



Sub pivot_test()


Dim pt As PivotTable
Dim pi As PivotItem

Set pt = ActiveSheet.PivotTables("PivotTable1")



For Each pi In _
pt.PivotFields("Mc Type").PivotItems
Select Case pi.Name
Case Is = "Q"
pi.Visible = True
Case Else
pi.Visible = False
End Select

Next pi


End Sub

 

[RoryA]

pi.visible = (pi.Name Like "*Q*")

should be enough. Note that Like is case-sensitive.

 

[bryanrobson]

Hi Rory. So I changed it to below and works like a dream. thanks very much for the quick reply.

Cheers


Sub pivot_test()


Dim pt As PivotTable
Dim pi As PivotItem

Set pt = ActiveSheet.PivotTables("PivotTable1")



For Each pi In _
pt.PivotFields("Mc Type").PivotItems
pi.Visible = (pi.Name Like "*Q*")


Next pi


End Sub

 

[RoryA]

Glad we could help.