How to Delete Sheet if Sheet Name Not in a List

Microsoft Excel is a powerful spreadsheet programme that is commonly used for data analysis, calculations, and information organisation. When working with Excel, you may encounter instances in which you need to eliminate sheets that are not in a predetermined list. This tutorial will walk you through removing sheets in Excel using VBA (Visual Basic for Applications) programming. Within Excel, VBA allows us to automate operations and create unique solutions. We can quickly detect and delete sheets that are not in our desired list by using VBA.

Deleting a Sheet If the Sheet Name Not in a List

Here we will first create a VBA module, then run it to complete the task. So let us see a simple process to know how you can delete a sheet if its name is not in a list in Excel.

Step 1

Consider any Excel workbook that contains a list of sheets on one sheet.

First, right?click on the sheet name and select View code to open the VBA application.

Right click > View code.

Step 2

Then click on Insert, select Module, and copy the below code into the text box.

Insert > Module > Copy.

Example

Sub Deletenotinlist()
    Dim i As Long
    Dim cnt As Long
    Dim xWb, actWs As Worksheet
    Set actWs = ThisWorkbook.ActiveSheet
    cnt = 0
    Application.DisplayAlerts = False
    For i = Sheets.Count To 1 Step -1
        If Not ThisWorkbook.Sheets(i) Is actWs Then
            xWb = Application.Match(Sheets(i).Name, actWs.Range("A2:A6"), 0)
            If IsError(xWb) Then
                ThisWorkbook.Sheets(i).Delete
                cnt = cnt + 1
            End If
        End If
    Next
    Application.DisplayAlerts = True
    If cnt = 0 Then
        MsgBox "Not find the sheets to be seleted", vbInformation, "Kutools for Excel"
    Else
        MsgBox "Have deleted" & cnt & "worksheets"
    End If
End Sub

In the code A2:A6, the range of cells containing the list of sheet names

Step 3

Then click F5 to run the code and click Ok. Then you can see that sheets that are not in the list will be deleted, and a message will be displayed.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can delete a sheet if its name is not in a list in Excel.