Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 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.

Delete a specific worksheet if exists in a workbook with VBA code


arrow blue right bubble Delete a specific worksheet if exists in a workbook with VBA code

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:

doc delete sheet if exist 1

4. And then click OK button:

(1.) If the sheet exists, it will be delete at once, see screenshot:

doc delete sheet if exist 2

(2.) If the sheet does not exist, a prompt box will pop out to remind you as following screenshot shown:

doc delete sheet if exist 3



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.