Hi All,
In one of the sheet we have a pivot table, for B3 cell we have a filter, need to select All and unselect "#N/A, (blanks), 0. Please find the below screenshot and do the needful in achieving this step.
To add criteria to a pivot item you need to create a new action in your excel vbo. The code, inputs/outputs are shown below. The problem is you have to set one value at a time to be either visible or not visible so this would mean you need to set a loop through to set the #NA, Blanks and 0 each to visible = FALSE leaving everything else in your pivot table. Hope this helps 🙂
Dim wb, ws As Object
'Dim SrcData As String
'Dim SrcSheet As String
'Dim SrcRange AS String
wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)
wb.Activate()
ws.Activate()
ws.PivotTables(Pivot_table_name).PivotFields(Pivot_field)'.CurrentPage = _
' "(All)"
With ws.PivotTables(Pivot_table_name).PivotFields(Pivot_field)
.PivotItems(Pivot_list_item).Visible = visible
End With
ws.PivotTables(Pivot_table_name).PivotFields(Pivot_field). _
EnableMultiplePageItems = True
