VBA Delete Sheet in Excel — Remove a Worksheet Without the Prompt or a Hang

TL;DR — Worksheet.Delete is the only sheet operation that interrupts to ask. It throws a modal confirmation — the one that says data may exist — and in an unattended macro or a loop that dialog is a hang, not a safety net. Silence it with Application.DisplayAlerts = False, and turn it back on in an error handler so a crash never leaves alerts off for the whole session. Two hard limits follow: Excel refuses to delete the last visible sheet (error 1004), and deleting by index inside a loop skips sheets unless you loop backwards.

Sub DeleteSheetSafely(nm As String)
    Dim ws As Worksheet
    On Error GoTo Done
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Worksheets
        If StrComp(ws.Name, nm, vbTextCompare) = 0 Then ws.Delete
    Next ws
Done:
    Application.DisplayAlerts = True      ' restored even if Delete raised an error
End Sub

Worksheet.Delete is the code behind right-click a tab ▸ Delete. It is how a macro tears down scratch sheets or last month's working tabs before a fresh run. Unlike Add and Copy, which do their work silently, Delete stops and waits for a human — and that pause is the root of almost every delete-sheet problem.

What you'll learn

The mental model — Delete is irreversible and asks first, and the question is what hangs your macro
The rule that matters most — silence the prompt, then restore it in an error handler
Why Excel refuses to delete the last visible sheet
Why deleting inside a loop skips sheets, and how looping backwards fixes it
How to delete a sheet only if it exists

The mental model: Delete asks first, and there is no undo

Two facts about Delete explain the rest. First, it is permanent — there is no Undo for a deleted sheet, so the data and everything on the tab is gone the instant the call succeeds. Second, precisely because it is permanent, Excel guards it with a modal confirmation dialog: "Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete."

In an interactive session that dialog is a helpful seatbelt. In automation it is the enemy. A macro that deletes ten scratch sheets throws that prompt ten times, and if the macro runs unattended — overnight, on a server, from a scheduled task — it simply freezes, waiting for a click that will never come. The whole art of deleting sheets in VBA is turning that question off safely.

The rule that matters most: silence the prompt, then restore it

Suppress the confirmation with Application.DisplayAlerts = False, and this is the important half, turn it back on in an error handler. Leaving alerts off is not a local convenience — it is a global setting that stays off for the rest of the Excel session, silencing every confirmation, including the "do you want to save over this file?" prompt. If your macro dies between switching alerts off and back on, you have quietly disarmed the whole application.

Sub ClearScratchSheets()
    On Error GoTo Done
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Scratch").Delete
Done:
    Application.DisplayAlerts = True      ' always runs, even on error
End Sub

This is the same discipline you use with ScreenUpdating and around Unprotect: any global you switch off gets restored at a single exit label so a crash cannot leave the session in a dangerous state. If you only ever remember one thing about deleting sheets, make it "restore DisplayAlerts in a handler."

Excel refuses to delete the last visible sheet

A workbook must always contain at least one visible sheet, so Delete on the last visible one raises run-time error 1004 — even with alerts suppressed. This bites the moment you write "delete every sheet that matches X" and X happens to match all of them, or "delete all sheets except the report" when the report is not where you assumed.

Guard the floor explicitly before deleting:

If ThisWorkbook.Worksheets.Count > 1 Then
    ws.Delete
Else
    MsgBox "Cannot delete the only remaining sheet."
End If

Note that hidden sheets do not count toward the "at least one visible" requirement, so a workbook with one visible and several hidden sheets still refuses to delete the visible one. If you need to remove it, unhide another sheet first.

Why deleting inside a loop skips sheets

Here is the trap that turns "delete these five sheets" into "delete two, skip three, then error." When you loop the collection by index and delete as you go, every deletion shifts the later indices down by one — so Sheets(i) after a delete points at what was Sheets(i+1), and your For i = 1 To Count walks straight past it:

' WRONG - deleting shifts the indices, sheets get skipped
For i = 1 To ThisWorkbook.Worksheets.Count
    ThisWorkbook.Worksheets(i).Delete       ' i now points past the next sheet
Next i

Two clean fixes. Loop backwards, so the shifting happens only in the part you already visited — the same reason you delete rows bottom-up (see VBA Delete Rows):

For i = ThisWorkbook.Worksheets.Count To 1 Step -1
    With ThisWorkbook.Worksheets(i)
        If .Name Like "Temp*" Then .Delete
    End With
Next i

Or use For Each, which is safe here because it iterates the objects rather than fixed index positions — that is the form the TL;DR snippet uses. Either way, never delete while stepping an index forward.

How ExcelMaster helps

Deleting a sheet looks trivial and hides four ways to fail: a modal prompt that hangs an unattended run, a global DisplayAlerts left off after a crash, error 1004 on the last visible sheet, and a forward loop that skips half its targets.

ExcelMaster lets you say what you want removed — "delete every sheet whose name starts with Temp, but never the last one" — and it writes the safe version: DisplayAlerts off with a guaranteed restore in an error handler, a count guard for the last-visible-sheet floor, and a backward loop or For Each so nothing gets skipped. It also reminds you that deletion is permanent, and offers to clear a sheet's contents instead when you want something reversible. You keep the workbook and the code.

Frequently asked questions

How do I delete a sheet in VBA without the confirmation prompt?

Set Application.DisplayAlerts = False before the delete, then restore it with Application.DisplayAlerts = True afterwards — ideally at an error-handler label so it runs even if the delete fails. With alerts suppressed, ThisWorkbook.Worksheets("Scratch").Delete removes the sheet silently.

Why does my macro freeze when deleting a sheet?

Because Delete throws a modal confirmation dialog and the macro is waiting for a click that never comes — common in unattended or scheduled runs. Suppress the dialog with Application.DisplayAlerts = False around the delete. Remember to turn it back on, since it stays off for the whole Excel session otherwise.

Why do I get error 1004 when deleting a worksheet?

Usually because it is the last visible sheet, and a workbook must keep at least one visible sheet. Check ThisWorkbook.Worksheets.Count > 1 before deleting, or unhide another sheet first. Note that error 1004 here is separate from the confirmation prompt — suppressing DisplayAlerts does not lift the last-sheet floor.

How do I delete a sheet only if it exists?

There is no Exists method, so loop the Worksheets collection and compare names with StrComp(ws.Name, nm, vbTextCompare) = 0, deleting on a match. This does nothing gracefully when the sheet is absent, instead of raising an error the way a direct Worksheets("Gone").Delete would.

How do I delete multiple sheets in a loop?

Loop backwards by index — For i = Worksheets.Count To 1 Step -1 — or use For Each, because deleting while stepping an index forward shifts the later indices and skips sheets. Suppress DisplayAlerts for the batch and guard the last-visible-sheet count so the final deletion does not throw 1004.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-14.