- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.