How to delete specific sheet if exists in workbook?
How could you delete a specific worksheet if it exists within a workbook? Normally, you need to find and check it if exists, and then delete it manually. Here, I will talk about an easy way to delete the specified worksheet directly if it exists in the Excel file.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
The following VBA code can help you to delete the specified worksheet if it exists in the workbook, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Delete a specific worksheet if exists in workbook
Sub Test() 'Updateby Extendoffice 20160922 Dim xWs As Worksheet Dim sheetName As String sheetName = Application.InputBox("Input Sheet Name:", "Kutools for Excel", _ "sheet1", , , , , 2) Application.DisplayAlerts = False Err.Clear On Error Resume Next Set xWs = Sheets(sheetName) If Err <> 0 Then MsgBox "The'" & sheetName & "'" & "does not exist!", vbInformation, "Kutools for Excel" Exit Sub Else xWs.Delete MsgBox "The'" & sheetName & "'" & "has been deleted!", vbInformation, "Kutools for Excel" End If Application.DisplayAlerts = True End Sub
3. Then press F5 key to run this code, a prompt box is displayed to remind you type the sheet name that you want to check if exist, see screenshot:
4. And then click OK button:
(1.) If the sheet exists, it will be delete at once, see screenshot:
(2.) If the sheet does not exist, a prompt box will pop out to remind you as following screenshot shown: