How to delete sheet if sheet name not in a list?
Let’s say, I have a worksheet which contains a list of sheet names within the workbook in column A as below screenshot shown, and some certain sheet names in the workbook are not in the list, now, I want to delete the sheets which are not in the list of column A. How could I solve this task in Excel quickly and easily?
Normally, you can check the sheet names one by one, and delete those are not in the sheet names list. But, here, I can talk about a VBA code to deal with it. Please do as follows:
1. Go to the worksheet which contains the sheet names list, and then hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Then click Insert > Module, and paste the following code in the Module Window.
VBA code: Delete sheets which are not in a list
Sub Deletenotinlist() 'Updateby Extendoffice 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
Note: In the above code, A2:A6 is the cell reference which contains the sheet names, you can change it to your need.
3. And then press F5 key to run this code, the sheets which are not in the list have been deleted at once, see screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 8 months agoHow could this be changed so if the sheet name contains the text in the list it does not delete them rather than if it matches.
- To post as a guest, your comment is unpublished.· 3 years agoHi,
My list of sheet names are numbers only and this code still deletes all the sheets names which are just number i.e I have sheets named "1", "2" and "3" and my list is 1 and 3 only, instead of deleting sheet "2" it deletes all numbered sheet. If the sheet name has any letters then the code works fine. Can someone help?!