Mrinmoy Roy
While deleting an Excel worksheet, it shows a prompt warning message. When you have only one worksheet to delete, you can easily manage a single prompt warning message. But when you have to delete a large number of Excel sheets, handling a prompt warning message for each of the sheets can be quite irritating. To fix the problem, in this tutorial, you will learn 5 different methods to delete a worksheet with no prompt message using Excel VBA.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
What Is a Prompt Warning Message?
The following VBA code creates a new Excel worksheet and deletes it when you run the code.
Sub SheetEraser()
ActiveSheet.Delete
End SubThe above VBA code deletes the active worksheet. But before deleting a worksheet, it will show a pop-up warning message regarding the sheet deletion like the following screenshot.
5 Methods to Delete a Worksheet with No Prompt Using Excel VBA
1. Delete a Single Worksheet with No Prompt Using the Excel VBA
You can use this method to delete only a single worksheet with no prompt warning message.
In the VBA code, just insert the name of the sheet that you want to delete, and then you are ready to go.
Now follow the steps below to delete a single worksheet with no prompt warning message.
❶ First, press ALT + F11 to open the VBA editor.
❷ Now create a new module from Insert > Module.
❸ Then copy the following VBA code.
Sub SheetEraser()
Application.DisplayAlerts = False
Sheets("Sheet7").Delete
Application.DisplayAlerts = True
End Sub❹ Now paste and save the above code in the VBA code editor.
❺ Now go to the Developer tab.
❻ From the Code group, select Macros.
This will open the Macro window. You can also press ALT + F8 to open the Macro dialog box.
❼ From the Macro dialog box, select the function name of the VBA code and hit Run.
This will instantly delete the sheet that you have inserted in the VBA code without showing any prompt warning message.
Read More: How to Delete a Sheet in Excel (5 Ways)
2. Delete an Active Worksheet with No Prompt Using the Excel VBA
This method deletes an active worksheet with no prompt warning box.
To delete an active worksheet,
❶ Press ALT + F11 to open the VBA editor.
❷ Now go to Insert > Module.
❸ After that copy the following VBA code.
Sub SheetEraser()
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub❹ Paste the code in the VBA editor.
❺ Now press the ALT + F8 buttons to open the Macro dialog box.
❻ From the dialog box, select the function name and hit the Run button.
This will instantly delete the active worksheet without showing any prompt warning box.
Related Content: How to Delete Multiple Sheets in Excel (4 Ways)
3. Delete Multiple Worksheets with No Prompt Using the Excel VBA
If you are looking for VBA codes to delete multiple worksheets without any prompt warning box, then follow this method.
❶ Hit ALT + F11 buttons to open the VBA editor.
❷ Go to Insert > Module.
❸ Copy the following VBA code:
Option Explicit
Sub DeleteSheets()
Dim x As Long
Application.DisplayAlerts = False
For x = 1 To Worksheets.Count
If Worksheets(x).Name Like "Sample *" Then Worksheets(x).Delete
Next x
Application.DisplayAlerts = True
End Sub❹ Paste and save the above code in the VBA editor.
❺ Press ALT + F8 to open the Macro window.
❻ Select the function name from the Macro dialog box and hit the Run button.
This will delete all the sheets started with the “Sample” keyword without showing any prompt warning box.
Read More: Shortcut to Delete Sheet in Excel (5 Quick Examples)
Similar Readings
- How to Undo Delete Sheet in Excel (3 Methods)
- Delete Excel Sheet Using VBA (10 Suitable Ways)
- How to Delete Excel Sheet Using VBA (10 Suitable Ways)
4. Pickup a Worksheet to Delete with No Prompt Using Excel VBA
You can use this method to manually insert a worksheet name and delete it with no prompt using Excel VBA.
❶ First press ALT + F11 to open the VBA editor.
❷ Click on the Module from the Insert tab.
❸ Copy the following VBA code:
Sub CheckThenDelete()
Dim x As Worksheet
Dim y As Variant
y = InputBox("Enter a Sheet Name to Delete: ")
Application.DisplayAlerts = False
For Each x In ThisWorkbook.Worksheets
If y = x.Name Then
x.Delete
End If
Next x
Application.DisplayAlerts = True
End Sub❹ Paste and save the code in the VBA editor.
❺ Press F5 to run the code.
❻ Insert a worksheet name that you want to delete in the pop-up dialog box.
❼ Finally hit the OK button to delete the sheet.
Read More: Excel VBA: Delete Sheet If It Exists (4 Methods)
5. Delete All the Worksheets Using the Excel VBA with No Prompt Warning Box
This method will delete all the worksheets in the active workbook without showing any prompt warning box.
To do that,
❶ Hit the ALT + F11 keys to open the VBA editor.
❷ Go to Insert > Module.
❸ Now copy the following VBA code:
Sub AllSheetsEraser()
Dim x As Worksheet
Dim y As String
y = "BlankSheet-" & Format(Now, "SS")
Sheets.Add.Name = y
Application.DisplayAlerts = False
For Each x In ThisWorkbook.Worksheets
If x.Name <> y Then
x.Delete
End If
Next x
Application.DisplayAlerts = True
End Sub❹ Press CTRL + V to paste and press CTRL + S to save to VBA code in the VBA editor.
❺ Now hit the F5 button to run the code.
This will delete all the worksheets in the active workbook without showing any prompt box.
Related Content: [Fixed!] Delete Sheet Not Working in Excel (2 Solutions)
Things to Remember
- Press ALT + F11 to open the VBA editor.
- To run the VBA codes, press the F5 button.
- You can press ALT + F8 to open the Macro window.
Conclusion
To sum up, we have discussed 5 methods to delete a worksheet with no prompt using Excel VBA. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
