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 Name Contains Specific Text from Workbook
Excel is a strong data analysis and manipulation tool, and we occasionally need to tidy up our workbooks by removing unneeded sheets. This tutorial will walk you through the procedure step by step if you have a large workbook with several sheets and want to swiftly delete sheets that satisfy certain criteria. In this article, we will look at a quick and easy way to erase sheets based on the text in their names. When dealing with enormous datasets or workbooks with multiple sheets, this strategy can save you a lot of time.
Delete Sheet if Name Contains Specific Text from Workbook
Here we will first create a VBA module and enter the specific text to complete the task. So let us see a simple process to know how you can delete a sheet if its name contains specific text from the workbook.
Step 1
Consider any Excel workbook where you have multiple sheets with similar names, as in the below image.
First, right-click on any sheet name and select View code to open the VBA application.
Step 2
Then click on Insert and select Module, then copy the below code into the text box.
Insert > Module > Copy.
Sub Deletebyname()
Dim shName As String
Dim xName As String
Dim xWs As Worksheet
Dim cnt As Integer
shName = Application.InputBox("Enter the specific text:", "Delete Sheet
Contain", _
ThisWorkbook.ActiveSheet.Name, , , , , 2)
If shName = "" Then Exit Sub
xName = "*" & shName & "*"
MsgBox xName
Application.DisplayAlerts = False
cnt = 0
For Each xWs In ThisWorkbook.Sheets
If xWs.Name Like xName Then
xWs.Delete
cnt = cnt + 1
End If
Next xWs
Application.DisplayAlerts = True
MsgBox "Have deleted" & cnt & "worksheets", vbInformation, "Delete Sheet
Contain"
End Sub
Step 3
Then click F5 to run the module, enter the text you want to delete, and click Ok to complete the task.
F5 > Text > Ok.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can delete a sheet if its name contains specific text from a workbook in Excel to highlight a particular set of data.
47 Views
