Macro to Delete Worksheets in Excel

These VBA macros delete worksheets: the active sheet, a sheet by name, the sheets you’ve selected, or every sheet whose name contains a word.

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Delete the active sheet

Excel normally asks you to confirm before deleting a sheet. This macro turns that message off for a moment:

Sub DeleteActiveSheet()
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End Sub

Delete a sheet by name

Sub DeleteSheetByName()
    Application.DisplayAlerts = False
    Worksheets("Sheet2").Delete
    Application.DisplayAlerts = True
End Sub

Replace Sheet2 with the sheet name. If no sheet has that name, the macro stops with an error.

Delete the selected sheets

Hold Ctrl and click several sheet tabs to select them, then run:

Sub DeleteSelectedSheets()
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
End Sub

Delete sheets whose name contains a word

Type part of a name, such as “Old”, and every matching sheet is deleted:

Sub DeleteSheetsContaining()
    Dim i As Long, txt As String
    txt = InputBox("Delete sheets whose name contains:")
    If txt = "" Then Exit Sub
    Application.DisplayAlerts = False
    For i = Worksheets.Count To 1 Step -1
        If InStr(1, Worksheets(i).Name, txt, vbTextCompare) > 0 Then Worksheets(i).Delete
    Next i
    Application.DisplayAlerts = True
End Sub

The loop runs from the last sheet to the first, so deleting a sheet doesn’t make it skip the next one. The match ignores upper and lower case.

Things to know

• A workbook must keep at least one visible sheet. Trying to delete the last one stops the macro.
• Deleted sheets can’t be restored with Undo. Save a copy first.
• Formulas on other sheets that pointed to a deleted sheet show #REF!.

Related macros