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.
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.
Delete a Pivot Table
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)