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.
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.
How to Delete a Worksheet with No Prompt Using Excel VBA: 5 Methods
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 you inserted in the VBA code without showing any prompt warning message.
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.
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.
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
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.
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.
Download Practice Workbook
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.
master….
ini ada macro untuk mengconvert menjadi value untuk 1 sheet saja.
bagaimana caranya untuk convert value untuk semua sheet yang ada (workbook)
‘This code will convert all formulas into values
Sub ConvertToValues()
With ActiveSheet.UsedRange
.Value = .Value
End With
End Sub
Thank you LUBIS for reaching us out. You can run the following VBA code to convert all formulas into values across all the sheets in the workbook.
This code utilizes a “For Each” loop to iterate through each worksheet in the workbook.
Regards
Aniruddah
Exceldemy