Excel VBA: Delete a PivotTable Safely

To delete a PivotTable with VBA, clear its full report range: use pt.TableRange2.Clear after setting pt to the PivotTable you want to remove. This removes the report from the worksheet, but it does not delete the source data or necessarily remove the PivotTable’s cache from the workbook.

What “delete a PivotTable” means

A PivotTable is a worksheet report built from source data and managed by Excel. Removing the report is different from deleting its source table, erasing the underlying records, or removing every copy of the PivotTable’s cached data. A macro that clears a PivotTable’s displayed range is intended to remove that report from the sheet; it does not reach back and delete the source records.

The range matters. TableRange2 covers the entire PivotTable report, including report-filter fields. TableRange1 covers the report but excludes those page fields. For removing a whole PivotTable, TableRange2 is generally the more complete choice. Clearing only some cells inside a PivotTable is not the same operation and may be blocked by Excel because the cells belong to a PivotTable.

Also distinguish removing a report from deleting its worksheet. Deleting a sheet removes everything on that sheet, not just the PivotTable, so use that approach only when the entire sheet is disposable.

Remove one named PivotTable with VBA

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and put a macro like this in the module. Replace the worksheet and PivotTable names with the exact names in your workbook.

Sub DeleteOnePivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Change these names to match your workbook.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set pt = ws.PivotTables("PivotTable1")

    ' Clear the complete report range.
    pt.TableRange2.Clear
End Sub

To find a PivotTable’s name, click inside it and look for the PivotTable Name box on the PivotTable Analyze tab. The worksheet name and PivotTable name are separate: a correct PivotTable name on the wrong worksheet will still cause the macro to fail. ThisWorkbook means the workbook containing the macro, which helps avoid accidentally acting on a different workbook that happens to be active.

On a Mac, open the Visual Basic Editor from the Developer tab’s Visual Basic command; the keyboard shortcut can depend on the keyboard and function-key settings. Excel for the web can display a workbook containing VBA, but it cannot run VBA macros. Use the desktop Excel app to run this code.

Delete every PivotTable on a worksheet

If the intention is to clear all PivotTables on one sheet, loop backward through the collection so that removing an item does not disrupt the remaining indexes. This example targets only the sheet named Report; it does not delete PivotTables on other sheets.

Sub DeleteAllPivotTablesOnReport()
    Dim ws As Worksheet
    Dim i As Long

    ' Change the worksheet name before running.
    Set ws = ThisWorkbook.Worksheets("Report")

    ' Work backward while removing PivotTables.
    For i = ws.PivotTables.Count To 1 Step -1
        ws.PivotTables(i).TableRange2.Clear
    Next i
End Sub

If a sheet contains no PivotTables, the loop simply has no iterations. If you need to remove PivotTables across the whole workbook, do not assume that clearing one sheet is enough: repeat the operation for each relevant worksheet, and review the workbook first so that reports you meant to keep are not removed.

Keep the displayed results instead of removing them

Sometimes the goal is to stop using a PivotTable but keep its current numbers and labels as a static report. Do not clear the PivotTable range first if those displayed results are the only copy you have. Instead, copy the report to a separate blank area or worksheet and use Paste Special > Values to create a values-only copy. Check that the copied output contains the rows, columns, labels, and totals you need before clearing the original.

For an ordinary PivotTable, a practical sequence is:

Copy the PivotTable report to a safe area or a new worksheet.
Use Home > Paste > Paste Special > Values on the copy. Depending on the ribbon layout, Paste Special may also be available from the paste menu’s values options.
Check that the copied values are correct and that required labels and totals are present.
Run the deletion macro on the original PivotTable only after confirming the copy.

This method avoids relying on the PivotTable to remain editable after its cells are cleared. If the layout includes special formatting, subtotals, merged cells, or other objects, inspect the copied result; a values-only copy will not preserve PivotTable behavior, and may not preserve the appearance you want. Keep a backup if the report is important.

What happens to the source data and PivotTable cache?

Clearing TableRange2 removes the visible report from the worksheet. It does not delete the original source range or Excel table used to build the report. It also does not automatically promise to remove the workbook’s PivotTable cache, which can retain data used by PivotTables. A cache may be shared by multiple PivotTables, so treating it as disposable without checking for other reports can cause problems.

This distinction is useful when a workbook is large or contains sensitive information. Deleting a report from a sheet is not the same as sanitizing the workbook or removing all stored copies of its data. If you need to remove source records, delete or change those records separately and consider every PivotTable and cache that may use them. If the requirement is to remove stored data from a workbook, inspect the workbook’s structure and sharing needs rather than assuming that clearing the displayed report accomplishes it.

A PivotChart associated with a PivotTable is another separate object to consider. Removing the report does not mean you should assume every related chart or workbook artifact has been removed as intended. Review the sheet after running the macro and remove any remaining chart or object deliberately if it is no longer needed.

Common errors and safer checks

A macro that works on one workbook can fail on another because names, protection, or selection differ. Check the following before running it:

Wrong name: Worksheet tab names and PivotTable names are not interchangeable. Confirm both in the workbook.
Protected worksheet: Sheet protection can prevent changes to the PivotTable. Unprotect the sheet through the workbook’s normal process before running the macro.
Wrong workbook: ThisWorkbook identifies the file holding the macro. If code is stored in a separate macro workbook, it may not be the workbook containing the report.
Shared or related reports: Removing one displayed PivotTable does not mean other PivotTables using the same source or cache are removed.
Unexpected formatting loss: The Range Clear method clears the range’s contents and formatting, not just its displayed values. Do not use it when you intend only to change the report’s style.

Save a separate copy of the workbook before running a destructive macro. VBA actions cannot be undone with Ctrl+Z, so a backup is the reliable way to recover the original if the wrong report or range is cleared. If you want to keep the macro in the file, save it in a macro-enabled format such as .xlsm; an ordinary .xlsx file does not retain VBA code.

When to use VBA versus Excel’s interface

For a one-time deletion, the interface may be safer because you can see which report is selected. Click a cell in the PivotTable, choose PivotTable Analyze > Select > Entire PivotTable, and press Delete. Review the selection before pressing the key. Ribbon labels can vary slightly across Excel builds and platforms, but the important step is selecting the entire report rather than a single cell.

VBA is more useful for a repeatable task, such as clearing a report before generating a fresh one, or removing several known PivotTables. The named-object method is more predictable than relying on whichever cell happens to be active. If you are uncertain, use the interface first or run a macro only after making a copy and verifying its worksheet and PivotTable targets.

Frequently Asked Questions

Can I delete a PivotTable without deleting the source data?
Yes. Clearing the PivotTable’s report range removes the displayed report, not the source worksheet or table. The source data remains unless you delete or change it separately.

Does TableRange2.Clear remove the PivotTable cache?
No, you should not treat it as a cache-removal command. It clears the PivotTable’s worksheet report range. A cache can remain in the workbook and may be shared with other PivotTables.

Why does Excel say I cannot change part of a PivotTable?
You are probably trying to clear or edit only part of the report. Select and remove the entire PivotTable, or use VBA to clear its full TableRange2, rather than changing an individual report cell.

Can I keep the numbers after deleting the PivotTable?
Yes. Copy the report to another area first and use Paste Special > Values to make a static copy. Confirm that the copied labels and totals are correct before clearing the original PivotTable.

Can I run this macro in Excel for the web?
No. Excel for the web can open workbooks that contain VBA, but VBA macros must be run in desktop Excel. Open the workbook in the Windows or Mac desktop app and follow that version’s macro prompts and security settings.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.