Delete Worksheets

To delete a worksheet in VBA, use the Worksheet.Delete method.

However, running it as-is can pop up a confirmation dialog, and it raises an error if the sheet does not exist, so a few points need care in practice.

This article covers the basic code, suppressing the confirmation message, checking for existence, deleting multiple sheets at once, and what to do when a sheet cannot be deleted.

Basic code for deleting a worksheet

Private Sub DeleteSheet()
  ThisWorkbook.Worksheets("Sheet1").Delete
End Sub

This code deletes the worksheet named “Sheet1” in the workbook that contains the macro.

If you write Worksheets("Sheet1") without specifying a workbook, the active workbook is the target. When several workbooks are open, specify ThisWorkbook (the workbook containing the macro) or ActiveWorkbook explicitly so you do not delete a sheet in the wrong workbook.

Delete without showing the confirmation message

Deleting a sheet that contains data displays a confirmation dialog (“Data may exist in the sheet(s) selected for deletion…”), and the macro stops until the user responds.

Setting Application.DisplayAlerts = False suppresses the dialog and deletes the sheet (the dialog’s default choice, “Delete”, is applied).

Private Sub DeleteSheetWithoutAlert()
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Sheet1").Delete
    Application.DisplayAlerts = True
End Sub

Delete a worksheet only if it exists

Calling Delete on a sheet name that does not exist raises run-time error 9 (Subscript out of range). Wrapping the existence check in a function lets you reuse it before every deletion.

Private Function SheetExists(ByVal sheetName As String) As Boolean
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If StrComp(ws.Name, sheetName, vbTextCompare) = 0 Then
            SheetExists = True
            Exit Function
        End If
    Next ws
End Function

Private Sub DeleteSheetIfExists(ByVal sheetName As String)
    If Not SheetExists(sheetName) Then
        MsgBox "Sheet '" & sheetName & "' does not exist."
        Exit Sub
    End If

    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(sheetName).Delete
    Application.DisplayAlerts = True
End Sub

You can also try Set ws = Worksheets(sheetName) under On Error Resume Next, but code that swallows errors tends to hide unrelated errors too, so a loop check is used here.

Delete multiple worksheets at once

Pass the sheet names as an array and delete them in a loop.

Private Sub DeleteMultipleSheets(ByVal sheetNames As Variant)
    Dim sheetName As Variant

    On Error GoTo Cleanup
    Application.DisplayAlerts = False
    For Each sheetName In sheetNames
        If SheetExists(CStr(sheetName)) Then
            ThisWorkbook.Worksheets(CStr(sheetName)).Delete
        End If
    Next sheetName

Cleanup:
    Application.DisplayAlerts = True
    If Err.Number <> 0 Then MsgBox Err.Description
End Sub

Sub ExecuteDeleteMultipleSheets()
    DeleteMultipleSheets Array("Sheet1", "Sheet2", "Sheet3")
End Sub

Sheets that do not exist are skipped by SheetExists, so processing finishes even if some are missing.

You cannot delete every sheet

A workbook must keep at least one visible sheet. Trying to delete all sheets fails on the last one. To replace sheets, add the new sheet first and then delete the old one.

Error handling

Where deletion might fail, add error handling with On Error GoTo. In particular, if an error occurs while DisplayAlerts is False, execution leaves the procedure before it is set back to True, so always restore it inside the error handler too.

Private Sub DeleteSheetWithErrorHandling(ByVal sheetName As String)
    On Error GoTo ErrorHandler

    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(sheetName).Delete
    Application.DisplayAlerts = True
    Exit Sub

ErrorHandler:
    Application.DisplayAlerts = True
    MsgBox "An error occurred while trying to delete the sheet: " & Err.Description
End Sub

Show your own confirmation before deleting

To prevent accidental deletion, display your own confirmation message.

Private Sub DeleteSheetWithConfirmation(ByVal sheetName As String)
    If Not SheetExists(sheetName) Then
        MsgBox "Sheet '" & sheetName & "' does not exist."
        Exit Sub
    End If

    If MsgBox("Do you really want to delete '" & sheetName & "'?", vbYesNo + vbQuestion, "Delete Confirmation") = vbYes Then
        Application.DisplayAlerts = False
        ThisWorkbook.Worksheets(sheetName).Delete
        Application.DisplayAlerts = True
    End If
End Sub

Why a worksheet cannot be deleted, and how to fix it

When the workbook structure is protected

You cannot add or delete sheets while “Protect Workbook Structure” is enabled. Remove the protection first, then delete.

Private Sub UnprotectWorkbookAndDelete(ByVal sheetName As String)
    ThisWorkbook.Unprotect Password:="yourpassword"
    DeleteSheetIfExists sheetName
    ThisWorkbook.Protect Password:="yourpassword", Structure:=True
End Sub

When the workbook is shared (legacy feature)

Sheets cannot be deleted in a workbook that uses the legacy “Share Workbook” feature. Switch to exclusive access with ExclusiveAccess (the workbook must be saved, and other users are disconnected), then delete.

Private Sub UnshareWorkbookAndDelete(ByVal sheetName As String)
    If ThisWorkbook.MultiUserEditing Then
        ThisWorkbook.ExclusiveAccess
    End If
    DeleteSheetIfExists sheetName
End Sub

Note that Share Workbook is now deprecated; migrating to co-authoring (real-time collaboration on OneDrive / SharePoint) is recommended.

Review questions

Private Sub DeleteSheetIfCondition()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Range("A1").Value = "Delete" Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws
End Sub

Private Sub DeleteSheetWithErrorHandling()
    On Error GoTo ErrorHandler
    Application.DisplayAlerts = False
    Worksheets("Sheet1").Delete
    Application.DisplayAlerts = True
    Exit Sub

ErrorHandler:
    Application.DisplayAlerts = True
    MsgBox "An error occurred while deleting the sheet: " & Err.Description
End Sub

Summary

• Delete a worksheet with Worksheets("SheetName").Delete, and specify the target workbook explicitly, e.g. with ThisWorkbook
• Hide the confirmation dialog with Application.DisplayAlerts = False, and always set it back to True
• Deleting a nonexistent sheet raises an error, so check that it exists first
• If deletion fails, suspect workbook structure protection or a shared workbook