How to Delete a Worksheet with No Prompt Using Excel VBA (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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 Sub

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

What is a Prompt Warning Message?


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.

Delete a Single Worksheet with No Prompt Using the Excel VBA

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

Delete an Active Worksheet with No Prompt Using the Excel VBA

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

Delete Multiple Worksheets with No Prompt Using the Excel VBA

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

Pickup a Worksheet to Delete with No Prompt Using Excel VBA

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

Delete All the Worksheets Using the Excel VBA with No Prompt Warning Box

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


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

2 Comments
  1. 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.

      Sub ConvertToValues()
      For Each ws In Worksheets
      With ws.UsedRange
      .Value = .Value
      End With
      Next
      End Sub

      This code utilizes a “For Each” loop to iterate through each worksheet in the workbook.

      Regards
      Aniruddah
      Exceldemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo