Md. Abdur Rahim Rasel
While working with a large Microsoft Excel, sometimes unnecessary data will appear in front of us, and we need to delete those unnecessary data and the sheet that contains the unnecessary data also in Excel. We also can do that by applying VBA Macros. Today, in this article, we’ll learn four quick and suitable ways that VBA delete sheet if name contains by applying the VBA Macros in Excel effectively.
Excel VBA to Delete Sheet If It Contains Name (Quick View)
Sub Delete_Sheets_with_Name()
'updated_by_Exceldemy(Rasel_0043)
Dim R As String
Dim N As Integer
N = Sheets.Count
Do While N > 0
R = Sheets(N).Name
If R = "Sales" Then
Application.DisplayAlerts = False
Sheets(N).Delete
Application.DisplayAlerts = True
End If
N = N - 1
Loop
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Suitable Ways to Delete Sheet If It Contains Name Using VBA in Excel
Let’s say, we have a dataset that contains information about several Sales representatives of the Armani group. The types of product and revenue earned by the Sales representatives are given in columns C, and D respectively. We will delete sheets according to the worksheet name by applying a simple VBA Code. Here’s an overview of the dataset for today’s task.
1. Use a VBA Code to Delete Sheet If It Contains Specific Name in Excel
In this method, we will delete the worksheet according to the worksheet name by applying the VBA Code. Applying the VBA code to delete the sheet is the easiest and the time-saving also. Let’s follow the instructions below to learn!
Step 1:
- First of all, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Delete Sheets with Name will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
Step 2:
- Hence, the Delete Sheet with Name module pops up. In the Delete Sheet with Name module, write down the below VBA.
Sub Delete_Sheets_with_Nmae()
‘Updated_by_Exceldemy_Rasel(0043)
Sheets("Product").Delete
End Sub
- After that, run the VBA To do that, go to,
Run → Run Sub/UserForm
- After running the code, a warning box named Microsoft Excel will appear instantly in front of you. From the Microsoft Excel dialog box, press the Delete option.
Step 3:
- Further, go back to your worksheet, and you will be able to delete the worksheet named product that has been given below screenshot.
Read More: Excel VBA: Delete Sheet If It Exists (4 Methods)
2. Delete Sheet According to Worksheet Number Using VBA Code in Excel
After learning the Delete Sheet with Name by applying the VBA code, now, we’ll learn how to delete Sheet with sheet’s Number from our dataset. Please follow the steps below to learn!
Step 1:
- According to method 1, insert a new module and type the below VBA code to delete cells. The VBA code is,
Sub Delete_Sheets_with_Sheet_Number()
Sheets(2).Delete
End Sub
- After that, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- After running the code, a warning box named Microsoft Excel will appear instantly in front of you. From the Microsoft Excel dialog box, press the Delete option.
Step 3:
- Further, go back to your worksheet, and you will be able to delete the worksheet with the sheet’s number that has been given below screenshot.
Read More: How to Delete a Worksheet with No Prompt Using Excel VBA (5 Methods)
Similar Readings:
3. Run a VBA Code with Loop to Delete Sheet If It Contains Name in Excel
After learning the above process by applying the VBA code, now, we’ll learn how to delete worksheets from our dataset by using a VBA Code with Loop. Please follow the steps below to learn!
Step 1:
- According to method 1, insert a new module and type the below VBA code to delete cells. The VBA code is,
Sub Delete_Sheets_with_Name()
'updated_by_Exceldemy(Rasel_0043)
Dim R As String
Dim N As Integer
N = Sheets.Count
Do While N > 0
R = Sheets(N).Name
If R = "Sales" Then
Application.DisplayAlerts = False
Sheets(N).Delete
Application.DisplayAlerts = True
End If
N = N - 1
Loop
End Sub
- Hence, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- Further, go back to your worksheet, and you will be able to delete the worksheet named Sales that have been given below screenshot.
Related Content: [Fixed!] Delete Sheet Not Working in Excel (2 Solutions)
4. Apply a VBA Code to Delete Sheet by Inserting the Sheet Name in Excel
Now, we will learn how to delete a sheet by inserting the name of the sheet in a titled box by applying a VBA Code. Let’s follow the instructions below to learn!
Step 1:
- According to method 1, insert a new module and type the below VBA code to delete cells. The VBA code is,
Sub Delete_Sheet()
'Updateby_Exceldemy_Rasel(0043)
           Dim R As String
           Dim M As String
           Dim sh As Worksheet
           Dim i As Integer
           R = Application.InputBox("Enter the Sheet Name:", "Exceldemy for Excel", _
           ThisWorkbook.ActiveSheet.Name, , , , , 2)
           If R = "" Then Exit Sub
           M = "*" & R & "*"
'Â Â Â Â Â Â Â Â Â Â MsgBox M
           Application.DisplayAlerts = False
           i = 0
           For Each sh In ThisWorkbook.Sheets
          If sh.Name Like M Then
          sh.Delete
          i = i + 1
          End If
           Next sh
           Application.DisplayAlerts = True
           MsgBox "Have deleted Entered Sheet" & i & "worksheets", vbInformation, "Exceldemy for Excel"
End SubSub Delete_Sheet()- Will create a sub-procedure named
Dim R As String
Dim M As String
Dim sh As Worksheet
Dim i As Integer- This code will declare dimension R, and M as string, sh as a worksheet, and i as an integer.
R = Application.InputBox("Enter the Sheet Name:", "Exceldemy for Excel", _ Â Â Â Â Â Â Â Â Â Â Â
ThisWorkbook.ActiveSheet.Name, , , , , 2)- Will helps to open an input box and the box heading is Exceldemy for Excel
Application.DisplayAlerts = False- This will disable the Application.DisplayAlerts to run the process in the background.
If sh.Name Like M Then          Â
sh.Delete          Â
i = i + 1 Â Â Â Â Â Â Â Â Â Â
End If- Will create a loop for integer value for the worksheet.
Application.DisplayAlerts = True- If the boolean value is true, it displays an alert and the message will run the VBA code.
MsgBox "Have deleted Entered Sheet" & i & "worksheets", vbInformation, "Exceldemy for Excel"- Create a message box titled Have deleted Entered Sheet.
- After that, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- After running the VBA code, an Exceldemy for Excel dialog box will appear in front of you. From Exceldemy for Excel dialog box, firstly, type the sheet name that you want to delete. Say, we want to delete the sheet whose name is Sales. At last, press OK.
- Hence, you will be able to delete the sheet whose name is Sales by showing a message Have deleted Entered Sheet1worksheets and pressing OK to go back to your VBA code.
Read More: How to Delete Excel Sheet Using VBA (10 Suitable Ways)
Things to Remember
👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.
👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,
File → Option → Customize Ribbon
👉 If you delete a sheet with a name, you can not undo it.
Conclusion
I hope all of the suitable methods mentioned above to delete sheets with names with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
