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:
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!