VBA Pivot Table Delete: What to Remove and How

Yes. In Excel for Microsoft 365 on Windows, remove a PivotTable report with VBA by clearing its full range using PivotTables("PivotTable1").TableRange2.Clear; there is no simple PivotTable.Delete method. Use ClearTable instead only if you want to empty the PivotTable while keeping its structure.

What “delete a PivotTable” can mean

People use “delete” to mean several different things in Excel, and choosing the wrong operation can remove more than intended. A PivotTable is a report built from source data, with a layout, calculated summary, and a connection to a PivotCache or other data source. Removing the report from a worksheet does not automatically delete the source data or guarantee that Excel removes the cache.

Remove the report: Clear the PivotTable’s entire displayed range. This is the usual meaning of deleting a PivotTable.
Empty the report but keep its shell: Clear the fields and layout while leaving a PivotTable object that can be configured again.
Remove displayed results but keep them as ordinary cells: Copy the results to a different location as values, then remove the PivotTable.
Remove the source data or cache: This is a separate cleanup task. Deleting a report does not necessarily remove a worksheet table, external connection, or cached data.

The distinction matters because Excel treats a PivotTable as a unified report. You generally cannot edit or clear just one cell inside it as though it were an ordinary range. To remove the report, target its whole range rather than a cell that happens to show one of its values.

Remove one PivotTable manually

For a one-time deletion, the ribbon is often safer than running a macro. Click a cell in the PivotTable, choose PivotTable Analyze > Select > Entire PivotTable, and press Delete. Confirm that the selection includes the whole report before removing it. The tab appears when the active cell is inside a PivotTable; Excel may show contextual tabs with slightly different placement depending on window size.

On Windows, the Delete key clears the selected report. On a Mac, use the keyboard’s Delete or Backspace key as appropriate for the keyboard; the key labeled Delete on many Mac keyboards acts like Windows Backspace. If you are unsure, use Excel’s ribbon command or menu rather than relying on a key label. Selecting the entire report is important: clearing a selected cell or a partial range can be blocked or may not remove the PivotTable structure.

To identify the PivotTable’s name before writing VBA, click inside it and look for the PivotTable Name box on the PivotTable Analyze tab. Excel assigns names such as PivotTable1, but users can rename them. Names are not necessarily the same as worksheet names, captions, or the source table’s name.

Delete one PivotTable with VBA

The following macro targets a PivotTable by its name on a specified worksheet. It clears TableRange2, which covers the full displayed PivotTable, including report filter fields when present. Replace the worksheet and PivotTable names with the exact names in your workbook.

Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. On Windows, run it from the Visual Basic Editor with the cursor inside the procedure and press F5, or run it through Excel’s Developer > Macros command. On a Mac, the Visual Basic Editor and macro controls are available too, though keyboard shortcuts and ribbon placement can differ.

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

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

    ' Clear the complete displayed PivotTable report.
    pt.TableRange2.Clear
End Sub

This code removes the report from the worksheet; it does not delete the source range. It also does not promise to erase a shared PivotCache, connection, or data-model content. If the worksheet is protected, the operation may fail until the sheet is unprotected. The macro uses ThisWorkbook, meaning the workbook that contains the code, rather than whichever workbook happens to be active.

Delete every PivotTable on a worksheet

If a report sheet contains several PivotTables, you can remove all of them in one pass. Loop backward through the PivotTables collection so that removing one report does not disrupt the numbering of items still to process.

Use this only when every PivotTable on the named sheet should go. Save a backup first, because this removes all of their displayed reports, not just the one currently selected.

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

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

    ' Work backward as each report is cleared.
    For i = ws.PivotTables.Count To 1 Step -1
        ws.PivotTables(i).TableRange2.Clear
    Next i
End Sub

This procedure only targets PivotTables on the specified worksheet. It does not remove PivotTables on other sheets, and it does not delete the worksheet itself. If the sheet has no PivotTables, the loop simply has no iterations to perform.

Clear a PivotTable without removing its structure

Sometimes the goal is to start over with the report layout, not to remove the PivotTable object. In that case, use the PivotTable object’s ClearTable method. It clears the report’s fields and layout, leaving an empty PivotTable report that can be rebuilt. This is different from clearing the entire cell range: the empty PivotTable remains available as a PivotTable.

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

    ' Use the worksheet and PivotTable names in your file.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set pt = ws.PivotTables("PivotTable1")

    ' Keep the PivotTable object, but clear its report layout.
    pt.ClearTable
End Sub

Use this option when you intend to reuse the same report object or want to clear the field arrangement before building a new view. If your requirement is for the report cells to disappear altogether, use TableRange2.Clear instead. A common misconception is that ClearTable deletes the PivotTable; its purpose is to clear the report, not remove its object.

Keep the results as ordinary values

Deleting a report also removes its displayed values. If someone needs to keep a snapshot, first copy the report to a different worksheet or location, then paste values there, and only then clear the original PivotTable. Pasting over the PivotTable’s own cells is not the right sequence because Excel restricts changes to cells that belong to a PivotTable.

For a manual snapshot, select the report, copy it, choose an empty destination, and use Home > Paste > Values. On Windows, Ctrl+C copies and Ctrl+V pastes; on Mac, use Command+C and Command+V. Check the pasted result before deleting the source report. A values-only copy preserves the numbers and labels as they appear at that moment, but it no longer refreshes when the underlying data changes. Formats, column widths, and some visual elements may require separate copying or adjustment.

For an automated workflow, VBA can copy the report to a separate destination and paste values there before clearing the original. That needs a deliberate destination range and rules for whether to overwrite existing data; a generic macro should not guess those choices. If the PivotTable contains report filters, confirm the copied range includes the information you need.

What happens to the source data and PivotCache?

Removing a PivotTable does not delete the worksheet cells, Excel table, database, or file that supplied its data. It also does not necessarily remove the PivotCache, which can store data used by a PivotTable. A cache may be shared by multiple PivotTables, so removing one report should not be treated as a reliable cache-cleanup command.

If the workbook is still large after you remove a report, check for other PivotTables, hidden or unused worksheets, workbook connections, and data-model content. Review Data > Queries & Connections for connections and queries, and inspect the workbook’s sheets for source data. Do not delete a connection or source table simply because one PivotTable is gone: another report or query may depend on it. The appropriate cleanup depends on where the data came from and whether other workbook features still use it.

Common errors and safer troubleshooting

If VBA reports that a PivotTable name is not valid, verify both names in the code. A typo in Worksheets("Report") causes a different problem from a typo in PivotTables("PivotTable1"). Also check whether the code is stored in the same workbook, since ThisWorkbook refers to the file containing the macro.

“Subscript out of range”: The worksheet name or workbook reference is incorrect. Confirm spelling, spaces, and which file contains the code.
PivotTable not found: Check the PivotTable Name box on PivotTable Analyze; do not assume its displayed caption is its object name.
Cannot change part of a PivotTable: The macro may be acting on a partial range. Target the PivotTable’s full TableRange2 rather than a single cell.
Protected worksheet: Unprotect it with the required password or permissions before clearing the report. A macro should not attempt to bypass protection.
Other objects remain: Slicers, shapes, charts, and buttons may be separate objects. Removing the PivotTable does not guarantee all related visual objects disappear.

Test a macro on a copy of the workbook before applying it to a production file. This is especially important when the sheet contains formulas, notes, or objects positioned beside the PivotTable. Review the target sheet after the macro runs, since a PivotTable’s footprint can change when filters or layout settings change.

Choosing the right method

Use the manual ribbon selection for a single report when you want to inspect the exact target first. Use TableRange2.Clear for a macro that removes a named PivotTable, or loop through the collection when you intentionally want to remove every report on one sheet. Choose ClearTable when the goal is an empty but reusable PivotTable. Make a values-only copy first if the displayed results are needed as a static record.

Before deleting anything, answer three questions: should the report itself disappear, must its displayed results be retained, and does the workbook still need the source or connection? Those answers determine whether to clear the report, preserve a snapshot, or perform a broader workbook cleanup. They also prevent a frequent mistake: assuming that removing the visible PivotTable automatically removes every piece of data associated with it.

Frequently Asked Questions

Can I use PivotTable.Delete in VBA?
There is no simple PivotTable.Delete method for removing the report. Clear its displayed range with TableRange2.Clear, or use ClearTable when you want the empty PivotTable object to remain.

What is the difference between TableRange1 and TableRange2?
TableRange1 covers the PivotTable report but excludes report filter fields. TableRange2 includes those fields, so it is generally the better target when removing the whole displayed PivotTable.

Does deleting a PivotTable delete its source data?
No. The PivotTable report and its source are separate. The source worksheet data, Excel table, query, or external source remains unless you remove it separately.

Can I undo a PivotTable deletion macro?
No. Excel’s normal Undo history does not reverse VBA changes. Save a separate copy before running the macro so you can restore the original if needed.

Will deleting one PivotTable remove its slicers?
Not necessarily. Slicers and other visual objects can exist separately and may be connected to more than one PivotTable. Review the sheet and remove any unneeded objects separately.

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.