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.
VBA To delete First sheet of the workbook irrespective of its name
VBA to delete the last sheet of the workbook
VBA to delete the ActiveSheet
VBA to delete a sheet with a specific name in a Workbook
VBA to Check if a Specific Sheet exists in a Workbook
VBA to delete multiple sheets from a Workbook
VBA to delete Sheet without Warning Message
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.
0 Comments
Trackbacks/Pingbacks