Macro to uncheck the (ALL) item in a pivot table field

[HKS]

How to write a macro to uncheck the (ALL) item in a pivot table field? After
that step, I will check the items that I want.

Thanks!

 

[HKS]

Actually what I want is a macro to check/select only few pivot items from a
pivot field that has more than hundred pivot items. Thanks!

 

[Avi]

It should be done other way
- Select(check) all the items
- uncheck the one you wish to filter out.

Sub filterPivotRowFileds()

cntItem = 0
Dim arrVisibleItems
arrVisibleItems = Array("1", "2") ' Fill in the items you want to
display in pivot

For Each pvtRowItem In
ActiveSheet.PivotTables("PivotTable1").PivotFields("Severity").PivotItems
' update the pivot name and row field name
pvtRowItem.Visible = True
cntItem = cntItem + 1
Next pvtRowItem


If cntItem < UBound(arrVisibleItems) Then
MsgBox "array has more items than listed in pivot"
Exit Sub
End If

For Each pvtRowItem In
ActiveSheet.PivotTables("PivotTable1").PivotFields("Severity").PivotItems
If Not (arrSearch(pvtRowItem.Value, arrVisibleItems)) Then
pvtRowItem.Visible = False
End If
Next pvtRowItem

End Sub

Public Function arrSearch(strSearch As String, arrStrToBeSearched As
Variant) As Boolean
For i = 0 To UBound(arrStrToBeSearched)
If strSearch = arrStrToBeSearched(i) Then
arrSearch = True
Exit Function
End If
Next i
arrSearch = False
End Function