WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Delete Sheet

VBA Delete Sheet

Excel VBA Delete Sheet

To Delete Sheet in VBA we a Delete Worksheet method. To apply this method first we need to identify which sheet we are deleting by calling the sheet name. We have two methods to do the same first we directly write sheet1.delete and second method is sheets(sheet1).delete.

So the syntax follows.

Worksheets("Worksheet Name").Delete

OR

Sheets("Sheet Name").Delete

So, first, we need to specify the sheet name by using either Worksheet or Sheets Object, then later, we can use the “Delete” method.

VBA Delete Sheet

How to Delete Excel Sheets using VBA Code?

You can download this VBA Delete Sheet Excel Template here – VBA Delete Sheet Excel Template

Example #1 – Delete Worksheet by using its Name

Assume you have many sheets, and to delete the particular worksheet, we need to mention the worksheet by its name. For example, I have 3 different sheets named “Sales 2016”, “Sales 2017”, and “Sales 2018”.

If I want to delete the sheet named “Sales 2017,” then I have to mention the sheet name like the below.

Code:

Sub Delete_Example1()

  Worksheets("sheets 2017").

End Sub

The problem with mentioning the worksheet name directly we don’t get to see the IntelliSense list of VBA. Anyways mention the method as “Delete.”

Code:

Sub Delete_Example1()

  Worksheets("sheets 2017").Delete

End Sub

So this will delete the sheet named “Sales 2017“.

Error While Deleting the Worksheet: If we try to delete the worksheet which does not exist or if we mention the worksheet name wrongly, then we will get the VBA error as “Subscript Out of Range.”

VBA Delete Worksheet Example 1

In the above, I got the “Subscript Out of Range” error because, in my workbook, there is no sheet name called “Sales 2017”.

Example #2 – Delete Worksheet by its Name with Variables

As we have seen in the above example, the moment we refer to the worksheet name by using the Worksheets object, we don’t get to see the IntelliSense list. To get to see the IntelliSense list, we need to use variables.

Step 1: First, declare the variable as Worksheet.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

End Sub

VBA Delete Worksheet Example 2

Step 2: Since the worksheet is an object variable, we need to set the variable to the specific worksheet by using the “SET” word.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  Set Ws = Worksheets("Sales 2017")

End Sub

VBA Delete Worksheet Example 2-1

Now the variable “Ws” refers to the worksheet named “Sales 2017”.

Step 3: Now, using the variable “Ws,” we can access all the IntelliSense list of the worksheet.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  Set Ws = Worksheets("Sales 2017")
  
  Ws.

End Sub

VBA Delete sheet Example 2-2

Step 4: From the IntelliSense list, select the “Delete” method.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  Set Ws = Worksheets("Sales 2017")

  Ws.Delete

End Sub

Example 2-3

Like this using variables, we can access the IntelliSense list.

Example 2-4

Example #3 – Delete Active Worksheet

Active Sheet is nothing but whichever worksheet active or selected at the moment. For this method, we need not mention the worksheet name. For example, look at the below VBA code.

ActiveSheet.Delete

Right now, the active sheet is “Sales 2017”.

Example 3

If I run the code, it will delete the active sheet, I.e., “Sales 2017”.

Now I will select “Sales 2016”.

Example 3-1

Now it will delete the active sheet i.e., “Sales 2016”.

Like this, we can use the “Active Sheet” object to delete the worksheet.

Note: To use this method, we need to absolutely sure of what we are doing with the Active Sheet and which sheet is going to be an active sheet.

Example #4 – Delete More than One Worksheet

In our above examples, we have seen how to delete a single sheet, but what if we have multiple worksheets? Let’s say we want to delete 10 worksheets.

We cannot keep writing 10 lines of code to delete the worksheet, so we need to use loops to loop through the collection of worksheets and delete them.

The below code will loop through the worksheets and delete all the worksheets in the workbook.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
    Ws.Delete
  Next Ws

End Sub

The above code will throw an error because it attempts to delete all the sheets in the workbook. So to avoid this, we need to retain at least one worksheet.

If we want to delete all the worksheets except the active sheet, then we need to use the below code.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
    If ActiveSheet.Name <> Ws.Name Then
      Ws.Delete
    End If
  Next Ws

End Sub

Similarly, if we don’t want to delete a specific worksheet but to delete all other worksheets, then we can use the below code.

Code:

Sub Delete_Example2()

 Dim Ws As Worksheet

 For Each Ws In ActiveWorkbook.Worksheets
   If Ws.Name <> "Sales 2018" Then 'You can change the worksheet name
     Ws.Delete
   End If
 Next Ws

End Sub

The above code will delete all the worksheets except the worksheet named “Sales 2018”.

Example 4

Recommended Articles

This has been a guide to VBA Delete Sheet. Here we learn how to delete worksheets using VBA coding along with some practical examples and a downloadable excel template. You may also have a look at other articles related to Excel VBA –

  • What is VBA Message Box (Yes/No)?
  • VBA Delete File
  • Delete Row in VBA
  • Protect Sheet with Excel VBA
  • VBA ByRef
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Delete Sheet Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More