Excel VBA delete worksheet based on worksheet name

Solution to delete worksheet based on worksheet name

I am providing the solution for the “Deluxe-Plus” version in the below code

To determine which worksheet is to delete, create an array called delWS(), and then loop through all the worksheet name to find the worksheet name that contains “DELETE” or “OLD”.

If (InStr(WS.Name, "DELETE") <> 0 Or InStr(WS.Name, "OLD") <> 0) And InStr(WS.Name, "KEEP") = 0

Instr function is to check whether the worksheet name contains the key words, the function returns the starting position of the key word in the worksheet name; return 0 if not found.

Application.DisplayAlerts = False  is used to prevent alert message before deleting worksheet.

 

Public Sub deluxPlus()
    Dim delWS() As Variant
    Dim WS As Worksheet
    'To store all the unwanted worksheets in array delWS()
    For Each WS In ThisWorkbook.Worksheets
       If (InStr(WS.Name, "DELETE") <> 0 Or InStr(WS.Name, "OLD") <> 0) And InStr(WS.Name, "KEEP") = 0 Then
           Count = Count + 1
           ReDim Preserve delWS(Count)
           delWS(Count) = WS.Name
       End If
    Next WS
    'To save all the unwanted worksheets name in variable alldelWS
    If Count > 0 Then
       For i = 1 To Count
           alldelWS = alldelWS & "," & delWS(i)
       Next i
    End If
    alldelWS = Right(alldelWS, Len(alldelWS) - 1)
    'To delete all worksheets in array delWS()
    If Count > 0 Then
       x = MsgBox("Are you sure you wanna delete them? " & vbNewLine & alldelWS, vbOKCancel)
       If x = vbOK Then
          For i = 1 To Count
              Application.DisplayAlerts = False
              Worksheets(delWS(i)).Delete
              Application.DisplayAlerts = True
          Next i
       End If
    End If
 End Sub