how to Delete Sheet if Name Contains Specific Text in VBA?

---

You can use the following syntax in VBA to delete each sheet in an Excel workbook that contains specific text:

Sub DeleteSheets()

    Dim TextToFind As String
    Dim TextWildcard As String
    Dim Ws As Worksheet
    Dim i As Integer
    
    'prompt user for text to search for in sheet names
    TextToFind = Application.InputBox("Delete Sheets That Contain: ", _
                                    ThisWorkbook.ActiveSheet.Name, , , , , 2)
                                    
    TextWildcard = "*" & TextToFind & "*"
    Application.DisplayAlerts = False
    
    'loop through sheets and delete each sheet that contains text
    i = 0
    For Each Ws In ThisWorkbook.Sheets
        If Ws.Name Like TextWildcard Then
            Ws.Delete
            i = i + 1
        End If
    Next Ws
    
    Application.DisplayAlerts = True
    
End Sub

When you run this particular macro, an input box will appear that you can type a specific text string in.

Once you press Enter, each sheet in the Excel workbook that contains that specific text will automatically be deleted.

Note: The line Application.DisplayAlerts=False tells VBA not to display the process of deleting the sheets, which makes it run quicker.

The following example shows how to use this syntax in practice.

Example: Use VBA to Delete Sheets that Contain Specific Text

Suppose we have the following Excel workbook that contains four sheets:

Now suppose that we would like to delete each sheet that contains “Team” in the sheet name.

We can create the following macro to do so:

Sub DeleteSheets()

    Dim TextToFind As String
    Dim TextWildcard As String
    Dim Ws As Worksheet
    Dim i As Integer
    
    'prompt user for text to search for in sheet names
    TextToFind = Application.InputBox("Delete Sheets That Contain: ", _
                                    ThisWorkbook.ActiveSheet.Name, , , , , 2)
                                    
    TextWildcard = "*" & TextToFind & "*"
    Application.DisplayAlerts = False
    
    'loop through sheets and delete each sheet that contains text
    i = 0
    For Each Ws In ThisWorkbook.Sheets
        If Ws.Name Like TextWildcard Then
            Ws.Delete
            i = i + 1
        End If
    Next Ws
    
    Application.DisplayAlerts = True
    
End Sub

When we run this macro, an input box appears where we can type in the text we’d like to search for in the sheet names:

Once we type in “Team” and press OK, the sheet names that contain “Team” anywhere in the name will automatically be deleted:

Notice that the two sheets that contained “Team” in the sheet name have been deleted.