Shows or hides an item or items in a PivotTable filter field.
Passing PivotTableName, Sheetname, workbook (assuming it is open), field name to be filtered, item (or list of items separated by |) that we want to show or hide and finally 1 or 2 to show or hide.
One piece of code had buried in my old tools.
Function PivotTable_ShowHideBlankItems(PivotName, PivotField, Show1_Or_Hide2, Optional WB = "This", Optional Shee="Active")
' Show or hide blank items in PivotTable for a certain field.
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = ActiveSheet.Name
Rett = 0
ShowTrue = True
If Show1_Or_Hide2 = 2 Then ShowTrue = False
On Error Resume Next
With Workbooks(WB).Worksheets(Shee).PivotTables(PivotName).PivotFields(PivotField)
.PivotItems("").Visible = ShowTrue
End With
If Err.Number = 0 Then Rett = 1
On Error Goto 0
Err.Clear
PivotTable_ShowHideBlankItems = Rett
End Function
' Show or hide blank items in PivotTable for a certain field.
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = ActiveSheet.Name
Rett = 0
ShowTrue = True
If Show1_Or_Hide2 = 2 Then ShowTrue = False
On Error Resume Next
With Workbooks(WB).Worksheets(Shee).PivotTables(PivotName).PivotFields(PivotField)
.PivotItems("").Visible = ShowTrue
End With
If Err.Number = 0 Then Rett = 1
On Error Goto 0
Err.Clear
PivotTable_ShowHideBlankItems = Rett
End Function
PivotName, PivotField, Show1_Or_Hide2, Optional WB = "This", Optional Shee="Active"
Views 123
Downloads 74
CodeID
DB ID
