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 Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
