Delete Worksheets

VBA is a powerful tool for automating work in Excel.

Especially in worksheet operations, using VBA allows you to efficiently process data.

This article will explain in detail the procedure for deleting worksheets using specific code examples.

Basic Code for Deleting a Worksheet

Below is basic VBA code for deleting a worksheet.

Private Sub DeleteSheet()
  Sheets("Sheet1").Delete
End Sub

This code deletes the worksheet named “Sheet1”.

However, if you run this code as is, a confirmation message will be displayed during deletion, interrupting the code execution.

To avoid this, set Application.DisplayAlerts = False, which will prevent the confirmation message from being displayed during deletion. After deletion, re-enable warning messages with Application.DisplayAlerts = True.

Private Sub DeleteSheet()
    Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
    Application.DisplayAlerts = True
End Sub

Delete Only When a Specific Worksheet Exists

I’ll also introduce a method to check if a specific worksheet exists before deleting it.

Private Sub DeleteSheetIfExists(sheetName As String)
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Sheets(sheetName)
    On Error GoTo 0
    If Not ws Is Nothing Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    Else
        MsgBox "Sheet " & sheetName & " does not exist."
    End If
End Sub

This code checks if a worksheet with the specified name exists and deletes it only if it does.

Deleting Multiple Worksheets at Once

There’s also a method for deleting multiple worksheets simultaneously.

Private Sub DeleteMultipleSheets(sheetNames As Variant)
    Dim sheetName As Variant
    Application.DisplayAlerts = False
    For Each sheetName In sheetNames
        On Error Resume Next
        Sheets(sheetName).Delete
        On Error GoTo 0
    Next sheetName
    Application.DisplayAlerts = True
End Sub

Before running this code, specify the worksheet names you want to delete as an array.

Sub ExecuteDeleteMultipleSheets()
    Dim sheetsToDelete As Variant
    sheetsToDelete = Array("Sheet1", "Sheet2", "Sheet3")
    DeleteMultipleSheets sheetsToDelete
End Sub

Error Handling

It’s important to implement error handling in case an error occurs.

Sub DeleteSheetWithErrorHandling(sheetName As String)
    On Error GoTo ErrorHandler
    Application.DisplayAlerts = False
    Sheets(sheetName).Delete
    Application.DisplayAlerts = True
    Exit Sub
ErrorHandler:
    MsgBox "An error occurred while trying to delete the sheet: " & Err.Description
    Application.DisplayAlerts = True
End Sub

Confirmation Message Before Deletion

Displaying a confirmation message before performing a delete operation can prevent accidental deletions.

Private Sub DeleteSheetWithConfirmation(sheetName As String)
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Sheets(sheetName)
    On Error GoTo 0
    If Not ws Is Nothing Then
        Dim confirmDelete As VbMsgBoxResult
        confirmDelete = MsgBox("Do you really want to delete " & sheetName & "?", vbYesNo + vbQuestion, "Delete Confirmation")
        If confirmDelete = vbYes Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Else
        MsgBox "Sheet " & sheetName & " does not exist."
    End If
End Sub

Troubleshooting Failed Worksheet Deletion

Possible causes for failed worksheet deletion include sheet protection, workbook protection, and shared workbooks. I’ll introduce solutions for each.

Private Sub UnprotectSheetAndDelete(sheetName As String)
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Sheets(sheetName)
    On Error GoTo 0
    If Not ws Is Nothing Then
        ws.Unprotect Password:="yourpassword"
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    Else
        MsgBox "Sheet " & sheetName & " does not exist."
    End If
End Sub

When Workbook Protection Is the Cause

When the entire workbook is protected, use the following code to unprotect it.

Sub UnprotectWorkbookAndDelete(sheetName As String)
    ThisWorkbook.Unprotect Password:="yourpassword"
    DeleteSheetIfExists sheetName
    ThisWorkbook.Protect Password:="yourpassword"
End Sub

When a Shared Workbook Is the Cause

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

Quiz

Private Sub DeleteSheetIfCondition()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Range("A1").Value = "削除" 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:
  MsgBox "シートの削除中にエラーが発生しました: " & Err.Description
End Sub

Summary

I’ve explained methods for deleting worksheets using VBA. Use these methods to further streamline your work in Excel.