How to delete sheet using Excel Macro

.

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.

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.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Trackbacks/Pingbacks

  1. Deleting Sheet without warning message - To enable deletion without any notification - […] task, which we try to do. I have already written an article where I have explained a lot about…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest