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?
Delete sheets which are not in a specific list with VBA code
Delete sheets which are not in a specific list with VBA code
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:
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
