How to delete a PivotTable using VBA?

To delete a PivotTable using VBA, you need to ensure that you’ve identified the correct PivotTable object and then delete it. Here’s how you can do it:

Identify the worksheet and the PivotTable you want to delete.
Use the `PivotTables` collection to reference the specific PivotTable.
Use the `Delete` method to remove the PivotTable.

Here is a sample VBA code to delete a PivotTable:

Sub DeletePivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim ptName As String

    ' Set your worksheet name
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Set your PivotTable name
    ptName = "PivotTable1"

    ' Check if the PivotTable exists
    On Error Resume Next
    Set pt = ws.PivotTables(ptName)
    On Error GoTo 0

    If Not pt Is Nothing Then
        ' Clear the range where the PivotTable is located
        pt.TableRange2.Clear

        ' Delete the PivotTable
        pt.TableRange2.Delete
    Else
        MsgBox "PivotTable '" & ptName & "' not found."
    End If
End Sub

Explanation:

`ws`: Represents the worksheet containing the PivotTable. Adjust `”Sheet1″` to the actual worksheet name.
`pt`: A variable that references the PivotTable.
`ptName`: The name of the PivotTable you want to delete. Make sure this matches the name in your workbook.
`pt.TableRange2.Clear`: Clears all data and formatting in the PivotTable range.
`pt.TableRange2.Delete`: Deletes the entire range occupied by the PivotTable.

Notes:

This method deletes the entire cell range occupied by the PivotTable, which is often necessary when removing a PivotTable.
Ensure that you have saved your work before running this code, as deleting a PivotTable cannot be undone.
If the PivotTable is not found, a message box will notify you.

Modify the worksheet name and PivotTable name as needed for your specific use case.

facebooklinkedin