Excel VBA Delete Worksheet: Code, Safety, and Options

To delete a worksheet with Excel VBA, call that worksheet’s Delete method. Excel normally displays a confirmation prompt; you can suppress it with Application.DisplayAlerts = False, but restore the setting afterward and check that you are deleting the intended sheet.

Delete a worksheet with a basic VBA macro

This simple macro deletes the worksheet named OldData from the workbook that contains the macro. Change the name inside the quotation marks to match the worksheet tab name exactly:

Sub DeleteOldData()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("OldData")
    ws.Delete
End Sub

When the macro runs, Excel usually asks you to confirm the deletion. Choosing the worksheet by name is more reliable than referring to it by position, such as Worksheets(2), because sheet positions can change when someone moves or adds tabs. The Delete method removes the worksheet and its contents from the workbook; it does not merely clear the cells.

Before running any deletion macro, save a separate copy of the workbook. Deleting a sheet with VBA cannot be reversed with Ctrl+Z, and the action can remove formulas, tables, charts, and other material stored on that sheet.

What the confirmation prompt does

The confirmation is a safeguard against accidental deletion. If you want the prompt to appear, leave Application.DisplayAlerts alone and let Excel show its standard warning. This is usually the better choice when a person runs the macro manually and should have a chance to reconsider.

For a routine process that deletes a known, disposable sheet, a macro can turn alerts off immediately before deletion. Excel then uses its default response to the deletion warning, which is to proceed. The setting affects Excel generally while it is off, not just the one worksheet, so code should turn it back on—or, better, restore its original value—after the operation. If a macro exits early or encounters an error without restoring the setting, other Excel actions may stop showing alerts.

Use a safer version that checks the workbook

This version checks that another visible worksheet will remain, temporarily suppresses the confirmation, and restores the prior alert setting even if deletion fails. It also reports an error, such as a missing worksheet name or a workbook structure restriction, instead of silently continuing:

Sub DeleteWorksheetSafely()
    Dim ws As Worksheet
    Dim candidate As Worksheet
    Dim oldAlerts As Boolean
    Dim hasOtherVisible As Boolean

    oldAlerts = Application.DisplayAlerts
    On Error GoTo HandleError

    Set ws = ThisWorkbook.Worksheets("OldData")

    ' Keep at least one other worksheet visible.
    For Each candidate In ThisWorkbook.Worksheets
        If candidate.Name <> ws.Name And candidate.Visible = xlSheetVisible Then
            hasOtherVisible = True
            Exit For
        End If
    Next candidate

    If Not hasOtherVisible Then
        MsgBox "Keep another worksheet visible before deleting this one.", vbExclamation
        Exit Sub
    End If

    Application.DisplayAlerts = False
    ws.Delete

CleanUp:
    Application.DisplayAlerts = oldAlerts
    Exit Sub

HandleError:
    MsgBox "Could not delete the worksheet: " & Err.Description, vbExclamation
    Resume CleanUp
End Sub

The visibility check is deliberately cautious: it prevents this macro from deleting a worksheet if no other visible worksheet remains. A workbook needs a visible worksheet, and Excel can block an operation that would leave none. The check concerns worksheets; a chart sheet is a different sheet type and does not count as a visible worksheet for this safeguard.

Choose the correct workbook and sheet reference

ThisWorkbook means the workbook where the VBA code is stored. That is generally right when the macro lives in the workbook being edited. If the code is stored in Personal.xlsb, an add-in, or another workbook, ThisWorkbook refers to that code-containing file instead—not necessarily the workbook currently selected on screen.

ActiveWorkbook means the workbook currently active in Excel. It can be useful for a macro intended to work on whichever workbook the user has selected, but it can also point to the wrong file if focus changes. For automated or important tasks, explicitly identify the target workbook rather than relying on whichever one happens to be active.

The Worksheets collection refers to worksheet tabs, while the broader Sheets collection can include chart sheets as well. Use Worksheets("OldData") when the target must be a cell-based worksheet. Names must match the tab caption, including spaces and punctuation. A worksheet can be hidden and still be addressed by name in VBA; it does not need to be visible to delete it.

Run the macro in Excel for Microsoft 365

VBA macros run in the desktop Excel application, not Excel for the web. To use the example in Excel for Microsoft 365 on Windows:

Save a backup copy of the workbook. If the file contains macros, save the working file as an Excel Macro-Enabled Workbook (.xlsm).
Open the Visual Basic Editor with Alt+F11, or choose Developer > Visual Basic on the ribbon. If Developer is not shown, enable the Developer tab through Excel Options.
In the editor, select the workbook project, choose Insert > Module, and paste the macro into the module.
Return to Excel, press Alt+F8, select the macro name, and choose Run. Review the result before saving over your original file.

On Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; the keyboard shortcut is commonly Option+F11, and some keyboards require Fn as well. Macro security settings may prevent a macro from running. Only enable macros for files you trust; do not lower security broadly just to run an unfamiliar workbook.

Understand what deletion can affect

Removing a worksheet can have effects beyond the visible tab. Formulas on other sheets that refer to deleted cells may change to #REF!. Charts, defined names, data validation sources, and other workbook features that depend on the deleted sheet may also be affected. External workbooks that link to it can lose their source, too. Excel does not provide a VBA deletion method that automatically preserves all these dependencies.

Workbook structure protection is another possible obstacle. If the workbook’s structure is protected, Excel may prevent sheets from being deleted, moved, renamed, hidden, or added. Unprotect the workbook through the appropriate password-authorized process before running the macro; worksheet cell protection is a separate feature and is not the same as protecting workbook structure.

For a repeatable process, consider adding safeguards before the deletion line: confirm that the target exists, verify the active workbook or expected file name, or ask the user to confirm. A macro that deletes by a fixed name is safer than one that deletes whichever tab is active, but the fixed name still needs to be checked against the correct workbook.

Delete, clear, hide, or move the worksheet?

Deleting is permanent within the current VBA run, so choose it only when the sheet and its data are no longer needed. If your goal is simply to remove old entries while keeping headings or formulas, clear the relevant cells instead. If people may need the data later, hiding the worksheet preserves it in the workbook. A hidden sheet remains accessible to VBA, and its visibility can be restored through Excel or code, depending on its visibility setting.

Moving or copying the worksheet to an archive workbook is another option when you need to retain a record but keep the main file tidy. This may be preferable for audit trails or recurring reports. Remember that moving a sheet can also affect formulas and links, so check dependent calculations after moving it. The right choice depends on whether the goal is to remove data, keep it out of view, or preserve a separate copy.

Common problems and how to fix them

“Subscript out of range”: The name in the code does not match a worksheet in the workbook referenced by the macro. Check spelling, spaces, and whether ThisWorkbook is the intended file.
The confirmation still appears: The deletion may be happening before alerts are disabled, or another part of the macro may restore them too soon. Set Application.DisplayAlerts = False immediately before deletion and restore the saved setting afterward.
The sheet will not delete: Check whether workbook structure is protected and whether another visible worksheet remains. Excel may prevent deletion if it would leave the workbook without a visible worksheet.
Excel stops showing other warnings: A macro may have left Application.DisplayAlerts set to False. Close and reopen Excel if necessary, and revise the macro to restore the previous value on every exit path.
The wrong tab disappears: Check the workbook reference and target name. Avoid relying on ActiveSheet for automated deletion unless the macro first verifies that the active sheet is exactly the intended target.

Frequently Asked Questions

Can VBA delete a worksheet without a confirmation prompt?
Yes. Set Application.DisplayAlerts to False immediately before calling Delete, then restore its original value afterward. That suppresses Excel’s warning, so use it only when the macro has identified the intended sheet.

Can I delete a worksheet by its tab name?
Yes. Use the worksheet’s name in the Worksheets collection, such as ThisWorkbook.Worksheets("OldData").Delete. The name must match the tab caption and the workbook reference must point to the intended file.

Can VBA delete the active worksheet?
Yes, with ActiveSheet.Delete, but that is riskier because the active tab may change. For a controlled macro, reference a specific worksheet by name and verify the target before deletion.

Can I undo a worksheet deletion with Ctrl+Z?
No. Excel’s normal Undo command does not undo a VBA macro’s worksheet deletion. Use a saved backup or another preserved copy to recover the sheet.

Can I run a VBA deletion macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the code.

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.