Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel VBA: Delete Sheet If It Contains Name (4 Ways)

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

excel vba delete sheet if name contains


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.

excel vba delete sheet if name contains


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

Use a VBA Code to Delete Sheet If It Contains Specific Name in Excel

  • 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

Use a VBA Code to Delete Sheet If It Contains Specific Name in Excel

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

Use a VBA Code to Delete Sheet If It Contains Specific Name in Excel

  • 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.

Use a VBA Code to Delete Sheet If It Contains Specific Name in Excel

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

Delete Sheet According to WorkSheet Number Using VBA Code in Excel

  • 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.

Delete Sheet According to WorkSheet Number Using VBA Code in Excel

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.

Delete Sheet According to WorkSheet Number Using VBA Code in Excel

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

Run a VBA Code with Loop to Delete Sheet If It Contains Name in Excel

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Run a VBA Code with Loop to Delete Sheet If It Contains Name in Excel

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 Sub
Explanation:
Sub 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.

Apply a VBA Code to Delete Sheet by Inserting the Sheet Name in Excel

  • 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.

Apply a VBA Code to Delete Sheet by Inserting the Sheet Name in Excel

  • 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.

Apply a VBA Code to Delete Sheet by Inserting the Sheet Name in Excel

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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo