- 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 Worksheet Based On Cell Value In Excel ?
Excel is an effective tool for organising and analysing data, and you might occasionally need to eliminate particular worksheets from your workbook based on predetermined criteria. This tutorial will show you how to automatically delete worksheets that match certain cell value criteria.
It can take a while to manually delete worksheets, especially when working with large workbooks that contain a lot of sheets. To erase worksheets depending on particular cell values, we can make use of the VBA (Visual Basic for Applications) programming language in Excel.
Delete Worksheet Based On Cell Value
Here, we will first create a VBA and then use it to complete the task. So let us see a simple process to know how you can delete worksheets based on cell values in Excel.
Step 1
Consider any Excel workbook that has multiple worksheets.
First, right−click on any sheet name and select "View Code" to open the VBA application. Then click on Insert, select Module, and copy the below code into the text box.
Right click > View code > Insert > Module > Copy.
Code
Sub deletesheetbycell() Dim shName As String Dim xName As String Dim xWs As Worksheet Dim cnt As Integer shName = Application.InputBox("Input the text to delete the sheets based on:", "Delete based value", _ "", , , , , 2) Application.DisplayAlerts = False cnt = 0 For Each xWs In ThisWorkbook.Sheets If xWs.Range("A1").Value = shName Then xWs.delete cnt = cnt + 1 End If Next xWs Application.DisplayAlerts = True MsgBox "Have deleted" & cnt & "worksheets", vbInformation, "Delete based value" End Sub
Step 2
Now save the sheet as a macro−enabled template and click F5 to run the module. Then enter the input text in the prompt and click OK to complete the task. You will see a confirmation prompt.
Save > F5 > Input text > Ok.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can delete worksheets based on cell values in Excel to highlight a particular set of data.