Removing Filters in a Pivot Field in Excel

Creation QR in Excel Removing Filters in a Pivot Field

When you want to remove a filter from a pivot table field, you can manually check the Show All box to show all items in that pivot table field. When you record this step, the code shows a list of all the items in the pivot field, instead of using a Show All command. When you run the code later, it can be very slow, if the list of items is long, such as in an OrderDate field. Also, if you add new items to the field and run the macro, the new items aren t made visible, because the macro contains a list of the original items only. This problem is based on the Filters.xlsm sample workbook.

Instead of using the recorded code, with its long list of items, you can use the ClearManualFilter method. For example, the following code shows all items, in all visible fields, in all tables on the active sheet. Store the code on a regular module. Sub ClearFilters () Dim pt As PivotTable Dim pf As PivotField Dim ws As Worksheet On Error GoTo err_Handler Set ws = ActiveSheet Application.ScreenUpdating = False Application.DisplayAlerts = False

For Each pt In ws.PivotTables For Each pf In pt.VisibleFields On Error Resume Next pf.ClearManualFilter Next pf Next pt exit_Handler: Application.ScreenUpdating = True Exit Sub err_Handler: MsgBox Err.Number & ": " & Err.Description GoTo exit_Handler Set pf = Nothing Set pt = Nothing Set ws = Nothing End Sub To run the code, use a method described in Section 11.1. The code refers to ActiveSheet, so you can run the code on any sheet that contains a pivot table.

The ClearFilters code uses For Each...Next loops to clear the manual filters in all visible fields, in all pivot tables on the active worksheet.

row fields, change pt.VisibleFields to pt.RowFields, and to show report filter fields, change pt.RowFields to pt.PageFields.

The ClearManualFilter method only clears the manual filters applied to the pivot field. You apply a manual filter by using the check boxes in the filter list, or by right-clicking an item, clicking Filter, and then clicking Hide Selected Items. In addition to the manual filters, date, value, or label filters may have been applied to the row or column fields. To clear value or Top 10 filters, you can use the following method: pf.ClearValueFilters To clear label or date filters, you can use the following method: pf.ClearLabelFilters

To clear all filters in the pivot table, you can use the following method: For Each pt In ws.PivotTables pt.ClearAllFilters Next pt

While recording a macro, you can record the ClearAllFilters method. Right-click a filtered field, click Tip

You discovered that one of the totals in your OrderDates report is incorrect, but you haven t received the corrected source data yet. You need to print the totals for the sales manager, who s leaving for the airport in 15 minutes. As a temporary fix, you d like to change the New York total and the Grand Total in the Values area of the pivot table, so you can print the report. However, when you try to type over a value, you see the error message Cannot change this part of a PivotTable report. This problem is based on the Change.xlsm sample workbook.

The best solution is to add a row in the source data, to adjust the New York total, with a comment to explain the entry. Then, refresh the pivot table, to see the updated total. After the corrected source data is received, another row can be added, to reverse the temporary adjustment. This solution would leave an audit trail, explaining the changes. However, if you change a PivotTable setting programmatically, you can make temporary changes to the PivotTable values. Store the code in a regular code module. Sub ChangePTValues() Dim pt As PivotTable Set pt = ActiveSheet.PivotTables(1) pt.EnableDataValueEditing = True Set pt = Nothing End Sub