{"id":12056,"date":"2011-10-11T10:09:52","date_gmt":"2011-10-11T10:09:52","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=18"},"modified":"2017-09-20T06:48:07","modified_gmt":"2017-09-20T06:48:07","slug":"how-to-delete-a-selected-sheet","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2011\/10\/how-to-delete-a-selected-sheet\/","title":{"rendered":"How to delete sheet using Excel Macro"},"content":{"rendered":"
Dear Friends, All possible cases of deleting a Sheet from Workbook using Excel VBA, are covered here.<\/p>\n <\/a><\/p>\n Using simple Note:<\/strong> Above function will always delete the first Worksheet from the workbook. <\/a><\/p>\n Using simple Note:<\/strong> Above function will always delete the last Worksheet from the workbook. <\/a><\/p>\n Using simple Note:<\/strong>This statement can be used for deleting any worksheet from the workbook using this workaround. <\/a><\/p>\n You can delete a Sheet with a specific Display name by using <\/a><\/p>\n 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.<\/p>\n Now you can call this function to know whether Sheet X<\/em><\/strong> exists.
\nWhile 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.
\nIn this article, I am going to teach you all about deleting the Sheets from a workbook<\/strong> through Excel VBA.
\nThere 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.<\/p>\nTopics covered in this Article<\/h1>\n
<\/i> VBA To delete First sheet of the workbook irrespective of its name<\/a><\/h2>\n
<\/i> VBA to delete the last sheet of the workbook<\/a><\/h2>\n
<\/i> VBA to delete the ActiveSheet<\/a><\/h2>\n
<\/i> VBA to delete a sheet with a specific name in a Workbook<\/a><\/h2>\n
<\/i> VBA to Check if a Specific Sheet exists in a Workbook<\/a><\/h2>\n
<\/i> VBA to delete multiple sheets from a Workbook<\/a><\/h2>\n
<\/i> VBA to delete Sheet without Warning Message<\/a><\/h2>\n<\/div>\n<\/div>\n
Excel VBA to Delete first Sheet of Workbook<\/h1>\n
Worksheets(1).Delete<\/code> 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.<\/p>\n
\r\nSub DeleteFirstSheet()\r\n\tOn Error GoTo err\r\n' Disable excel alerts sent while deleting a sheet\r\n\tApplication.DisplayAlerts = False\r\n\tWorksheets(1).Delete\r\n\tMsgBox (\"First is successfully deleted\")\r\n\terr:\r\n\tApplication.DisplayAlerts = True\r\nEnd Sub\r\n<\/code><\/pre>\n
\nIf 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.<\/em><\/p>\n Excel VBA to Delete Last Sheet of Workbook<\/h1>\n
Worksheets(1).Delete<\/code> 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.<\/p>\n
\r\nSub DeleteFirstSheet()\r\n On Error GoTo err\r\n' Disable excel alerts sent while deleting a sheet\r\n Application.DisplayAlerts = False\r\n Worksheets(Worksheets.Count).Delete\r\n MsgBox (\"First is successfully deleted\")\r\nerr:\r\n Application.DisplayAlerts = True\r\nEnd Sub\r\n<\/code><\/pre>\n
\nIf 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.<\/em><\/p>\n Excel VBA to Delete ActiveSheet from the Workbook<\/h1>\n
ActiveSheet.Delete<\/code> statement will be able to delete activesheet from your workbook. Refer the below function where this statement is used to delete the ActiveSheet.<\/p>\n
\r\nSub DeleteActiveSheet()\r\n\tOn Error GoTo err\r\n' Disable excel alerts sent while deleting a sheet\r\n\tApplication.DisplayAlerts = False\r\n\tActiveSheet.Delete\r\n\tMsgBox (\"ActiveSheet is successfully deleted\")\r\n\terr:\r\n\tApplication.DisplayAlerts = True\r\nEnd Sub\r\n<\/code><\/pre>\n
\n1. First activate the Worksheet which you want to delete
\n2. Then you can use this ActiveSheet.Delete statement<\/p>\nVBA to delete sheet from Workbook – With specific name<\/h1>\n
Sheets(\"SheetNameToBeDeleted\").Delete <\/code>.
\nIf 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.
\nTo 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.<\/p>\nExcel VBA to check – if a given Sheet Exists in Workbook<\/h2>\n
\r\nFunction DoesSheetExists(SheetX) As Boolean\r\n Dim SheetExists As Boolean\r\n DoesSheetExists = False\r\n For Each Sheet In Sheets\r\n If UCase(Sheet.name) = UCase(SheetX) Then\r\n DoesSheetExists = True\r\n Exit Function\r\n End If\r\n Next Sheet\r\nEnd Function\r\n<\/code>\r\n<\/code><\/pre>\n
\nNow here is the actual code which will delete the specific Sheet where I have used the above function.<\/p>\n\r\nSub DeleteSheetX()\r\n On Error GoTo err\r\n Application.DisplayAlerts = False\r\n\t'Call the DoesSheetExists function to check if sheets exists\r\n If (DoesSheetExists(\"MySheetNameToDelete\") = True) Then\r\n Worksheets(\"MySheetNameToDelete\").Delete\r\n MsgBox (\"Selected Sheet is successfully deleted\")\r\n Else\r\n MsgBox (\"This Sheet does not exists\")\r\n End If\r\nerr:\r\n Application.DisplayAlerts = True\r\nEnd Sub\r\n<\/code><\/pre>\n