How to delete an Excel sheet with VBA?

To delete an Excel sheet using VBA, you can use the following steps to write a simple macro. Here’s how you can do it:

   Sub DeleteSheet()
       Dim ws As Worksheet
       On Error Resume Next
       Set ws = ThisWorkbook.Sheets("Sheet2")
       On Error GoTo 0
       
       If Not ws Is Nothing Then
           Application.DisplayAlerts = False
           ws.Delete
           Application.DisplayAlerts = True
       Else
           MsgBox "Sheet not found."
       End If
   End Sub

Open Excel and press `ALT` + `F11` to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, you can either work with an existing module or insert a new one. To insert a new module:

Go to the menu and click on Insert > Module. This will create a new module in your workbook.

In the module window, you can write a VBA subroutine to delete a specific sheet. Here’s an example code to delete a sheet named “Sheet2”:
Ensure that you have saved your work because deleting sheets cannot be undone.
Run the macro:

You can run the macro by pressing `F5` with the module selected, or by going back to Excel, pressing `ALT` + `F8`, selecting the macro `DeleteSheet`, and clicking Run.

Explanation:

The code first tries to set a worksheet object `ws` to the sheet with the name “Sheet2”.
`On Error Resume Next` allows the code to continue even if there is an error (e.g., the sheet is not found).
`Application.DisplayAlerts` is set to `False` to prevent Excel from showing the confirmation dialog when deleting a sheet, which allows the macro to run smoothly without user interaction.
`ws.Delete` actually deletes the specified sheet.
Finally, `Application.DisplayAlerts` is set back to `True` to re-enable alerts.

Make sure to change “Sheet2” to the name of the sheet you want to delete.

facebooklinkedin