Excel VBA: Deleting a worksheet in code

  • Article

I wanted to take a moment and share a little VBA in Excel that would delete a worksheet from code. Usually, you would want to use this as a part of a larger routine.

To call the Sub, use this syntax:
DeleteWorksheet "xxx", False
Where xxx represents the name of the worksheet, and False (or True) represents whether or not a confirmation is displayed with the deletion to the user.

Here is the code itself:

 
Sub DeleteWorksheet(strWorksheet As String, booAlerts As Boolean)
'Deletes a worksheet.  Usage: DeleteWorksheet (name of worksheet) (show alerts/prompts)
'Example: DeleteWorksheet "MyWorksheet", False
' will delete a Worksheet called MyWorkSheet if it exists and suppress prompts for the user.
    Dim strTempWorksheetName As String
    Dim intPOS, intSheets As Long
    intSheets = Sheets.Count

    For intPOS = intSheets To 1 Step -1
        strTempWorksheetName = Sheets(intPOS).Name
        If strTempWorksheetName = strWorksheet Then
        If booAlerts = False Then Application.DisplayAlerts = False
        Sheets(intPOS).Delete
        If booAlerts = False Then Application.DisplayAlerts = True
        End If
    Next intPOS
End Sub

— Easy link to my blog: https://aka.ms/leesteve —
If you like my blogs, please share it on social media and/or leave a comment.