VBA: Delete Sheet if Name Contains Specific Text


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.

All sheets that did not contain “Team” were left untouched.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Number of Sheets in Workbook
VBA: How to Extract Data from Another Workbook
VBA: How to Insert Multiple Rows

One Reply to “VBA: Delete Sheet if Name Contains Specific Text”

  1. very useful (solved an issue I was having)

    however I do have to ask what the purpose was for the counter?
    seems to work perfectly without

    Application.DisplayAlerts = False

    For Each Ws In ThisWorkbook.Sheets
    If Ws.Name Like TextWildcard Then
    Ws.Delete
    End If
    Next Ws

    Application.DisplayAlerts = True

Leave a Reply

Your email address will not be published. Required fields are marked *