Dear Friends,
While automating in Excel through Excel VBA, you may want to delete or add some Worksheets. This is a very common task, which we try to do.
In this article, I am going to teach you all about deleting the Sheets from a workbook through Excel VBA.
There are many scenarios – based on how and which sheet do you want to delete it from Workbook. I will explain you each one of them by providing an Example VBA Code which you can directly use them in your VBA programs.

Topics covered in this Article

All possible cases of deleting a Sheet from Workbook using Excel VBA, are covered here.

Excel VBA to Delete first Sheet of Workbook

Using simple Worksheets(1).Delete statement will be able to delete the first Worksheet from your workbook. Refer the below function where this statement is used to delete the First Worksheet.


Sub DeleteFirstSheet()
	On Error GoTo err
' Disable excel alerts sent while deleting a sheet
	Application.DisplayAlerts = False
	Worksheets(1).Delete
	MsgBox ("First is successfully deleted")
	err:
	Application.DisplayAlerts = True
End Sub

Note: Above function will always delete the first Worksheet from the workbook.
If there is only one sheet in the workbook, then it will do nothing because last one sheet can not be deleted from any workbook. At least one Sheet should remain in it.

Excel VBA to Delete Last Sheet of Workbook

Using simple Worksheets(1).Delete statement will be able to delete the first Worksheet from your workbook. Refer the below function where this statement is used to delete the First Worksheet.


Sub DeleteFirstSheet()
    On Error GoTo err
' Disable excel alerts sent while deleting a sheet
    Application.DisplayAlerts = False
    Worksheets(Worksheets.Count).Delete
    MsgBox ("First is successfully deleted")
err:
    Application.DisplayAlerts = True
End Sub

Note: Above function will always delete the last Worksheet from the workbook.
If there is only one sheet in the workbook, then it will thrown an error and do nothing because last one sheet can not be deleted from any workbook. At least one Sheet should remain in it. Since in the above code, error is handled, hence you would not get any error.

Excel VBA to Delete ActiveSheet from the Workbook

Using simple ActiveSheet.Delete statement will be able to delete activesheet from your workbook. Refer the below function where this statement is used to delete the ActiveSheet.


Sub DeleteActiveSheet()
	On Error GoTo err
' Disable excel alerts sent while deleting a sheet
	Application.DisplayAlerts = False
	ActiveSheet.Delete
	MsgBox ("ActiveSheet is successfully deleted")
	err:
	Application.DisplayAlerts = True
End Sub

Note:This statement can be used for deleting any worksheet from the workbook using this workaround.
1. First activate the Worksheet which you want to delete
2. Then you can use this ActiveSheet.Delete statement

VBA to delete sheet from Workbook – With specific name

You can delete a Sheet with a specific Display name by using Sheets("SheetNameToBeDeleted").Delete .
If you simply use the above statement and just in case, sheet with such a specific display name is not available in that workbook, then it will throw an exception.
To overcome this problem and throw a proper error message, you should first check if there is any Sheet with a given specific names exists. If, it exists then delete else you can throw an error message to the user.

Excel VBA to check – if a given Sheet Exists in Workbook

In the below function, all you need to pass is the name of your WorkSheet, which you want to check. This function will return a Boolean – True or False as a result.


Function DoesSheetExists(SheetX) As Boolean
    Dim SheetExists As Boolean
    DoesSheetExists = False
    For Each Sheet In Sheets
        If UCase(Sheet.name) = UCase(SheetX) Then
            DoesSheetExists = True
            Exit Function
        End If
    Next Sheet
End Function

Now you can call this function to know whether Sheet X exists.
Now here is the actual code which will delete the specific Sheet where I have used the above function.


Sub DeleteSheetX()
    On Error GoTo err
    Application.DisplayAlerts = False
	'Call the DoesSheetExists function to check if sheets exists
    If (DoesSheetExists("MySheetNameToDelete") = True) Then
        Worksheets("MySheetNameToDelete").Delete
        MsgBox ("Selected Sheet is successfully deleted")
    Else
        MsgBox ("This Sheet does not exists")
    End If
err:
    Application.DisplayAlerts = True
End Sub

VBA to delete multiple Sheets at once

Sheets(Array(….)).Delete can be used to delete multiple sheets using once statement.
All you need to use an Array keyword and provide the names of all the sheets you want to delete.
Example:
Sheets(Array("Sheet1","Sheet2", "Sheet3")).Delete = This will delete Sheet1, Sheet2 and Sheet3
Sheets(Array(1,2,3)).Delete = This will delete 1st, second and 3rd Sheets of the workbook.


Sub DeleteArrayOfSheets()
    On Error GoTo err
    Application.DisplayAlerts = False
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
err:
    Application.DisplayAlerts = True
End Sub

Note: If any one of the Sheets which are mentioned in the array, does not exist then, it will throw an error and none of the sheets will be deleted – even if other sheets were existing.

VBA to Delete Sheet without Warning Message

In excel, as soon as you try to delete a sheet, there is an inbuilt warning message which is popped up to confirm, if you really want to delete the sheet.
You definately do not want to see that popup – every time when your VBA code is running and trying to delete a sheet. Once the popup appears, VBA execution will wait untill you manually confirm the popup.

VBA to disable Delete confirmation Alert popup

It is simple to supress this confimation popup through Excel VBA. All you need to do is execute this statement Application.DisplayAlerts=False.
This is the reason, in all the above codes, if you notice, I have used this statement to suppress this delete confimation alert popup.
Do not forget to set it to true at the end of the function. If you fail to do so, then even when you try to delete a sheet manually, then also you would not receive this confirmation popup – which you may do not want.

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!