Filter Pivot Field

Karthik.choppadandi — Thu, 22 Aug 2024 14:44:15 GMT

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.

Re: Filter Pivot Field

Mohamad_Dakkouri — Thu, 22 Aug 2024 20:00:23 GMT

Hi @Karthik.choppadandi

Is it possible to use vba ?

Re: Filter Pivot Field

michaeloneil — Thu, 22 Aug 2024 21:03:40 GMT

Hi @Karthik.choppadandi

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

topic Filter Pivot Field in Digital Exchange

Filter Pivot Field

Re: Filter Pivot Field

Re: Filter Pivot Field