How to: Clear or Remove a Pivot Table

  • 2 minutes to read

The examples below demonstrate how to clear a pivot table by removing all its fields or completely delete a report from a worksheet.

Select the task you wish to perform.

Clear a Pivot Table

To remove all fields and formatting from a PivotTable report, use the PivotTable.Clear method. This method resets the pivot table to the initial state before any fields are added to it, but does not delete the report. The data connection, PivotCache, and the report location are preserved.

NOTE

If a pivot table you wish to clear is based on the same data as other pivot tables in a document, the PivotTable.Clear method will also remove grouping, calculated fields, and calculated items from other shared PivotTable reports.

Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet

' Clear the pivot table.
worksheet.PivotTables("PivotTable1").Clear()

The following image shows the result of the code's execution. A blank pivot table is displayed so you can start designing the report's layout all over again.

DXSpreadsheet_PivotTables_ClearAll

Delete a Pivot Table

To delete a PivotTable report from a worksheet, remove it from the worksheet's PivotTableCollection by using the PivotTableCollection.Remove or PivotTableCollection.RemoveAt method.

Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Remove the pivot table from the collection.
worksheet.PivotTables.Remove(pivotTable)