To delete multiple sheets in Excel, follow the steps below:
- Select the sheets to delete.
- Right-click on any of the selected sheets.
The sheet context menu will appear. - Click on Delete.
This will delete the selected sheets.
Deleting multiple sheets in Excel is necessary when there are redundancy, organization, storage, or security problems due to unnecessary sheets. Deleting unnecessary sheets is necessary to prevent larger file sizes and better indexing the sheets.
In this Excel tutorial, you will learn to delete multiple sheets in Excel using the Ribbon command, VBA code, etc. Additionally, we will show you how to delete multiple sheets based on different criteria.
Here I have an Excel file with some sales and profit sheets. I’ve deleted multiple sales sheets as shown in the following image.
3 Ways to Delete Multiple Sheets in Excel
Excel provides 2 built-in commands to delete sheets that you can find from the Home tab and context menu. Apart from these commands, you can also use the VBA method to develop a one-click solution for repetitive processes and a high number of sheets.
To delete multiple sheets in Excel, here are the 3 methods:
Using Delete Sheet Command From the Ribbon
Using the Home tab in Excel to delete sheets is easy and convenient, especially for users who prefer the ribbon interface over right-clicking on sheet tabs.
To delete multiple sheets using the Ribbon Command, follow the steps below:
- Select the sheets to delete.
- For adjacent sheets, hold Shift > first sheet > last sheet.
- For non-adjacent sheets, hold Ctrl and select the sheets one by one.
- Go to the Home tab > Cells group > Delete drop-down > Delete Sheet.
A confirmation dialogue box will appear. - Click on Delete.
This will delete the selected sheets.
Using Context Menu
If you have a small number of sheets to delete, or when the sheets to be deleted are easily identifiable by their names or positions, then this method will be beneficial to you.
To delete multiple sheets using the context menu, follow the steps below:
- Select the sheets to delete.
- For adjacent sheets, hold Shift > first sheet > last sheet.
- For non-adjacent sheets, hold Ctrl and select the sheets one by one.
- Right-click on any of the selected sheets.
The sheet context menu will appear. - Click on Delete or press D on the keyboard.
This will delete the selected sheets without a warning or a confirmation box.
Using VBA Code
VBA code will come in handy when you need to delete a large number of sheets, especially if they follow a specific pattern. It’s also useful for repeated tasks in the same or across multiple workbooks.
To delete multiple sheets using the VBA code, follow the steps below:
- Press Alt+F11 to open the Microsoft Visual Basic for Applications window.
Or, go to the Developer tab > Code group > Visual Basic.
- Select Insert > Module.
The Module window will appear. - Insert the following code in the Module window:
Sub Delete_Multiple_Worksheets() 'Turn off the display alerts Application.DisplayAlerts = False 'Deleting multiple worksheets Worksheets("Sales1").Delete Worksheets("Profit1").Delete 'Turn on the display alerts back Application.DisplayAlerts = True End Sub
- Click on the Run icon or press F5.
This will delete all the sheets mentioned in the VBA code.
Note: To get the confirmation dialog boxes, exclude the following lines from the code:
Application.DisplayAlerts = False
Application.DisplayAlerts = True
How to Delete Multiple Sheets Based on Conditions in Excel?
There are several cases where you may have to delete multiple sheets based on certain conditions such as deleting all sheets except the active one or deleting a group of sheets with specific names or strings. Using the VBA code is most effective in such scenarios since using the Excel features is a lengthy process for a large number of sheets.
Here are the two cases of deleting multiple sheets based on conditions in Excel:
Case 1: Delete All Sheets Except the Active Sheet
In this example, the current sheet is the “Profit1“. So, we will delete all the sheets except this one.
To delete all sheets except the active sheet in Excel, follow the steps below:
- Press Alt+F11 to open the Microsoft Visual Basic for Applications window.
Or, go to the Developer tab > Code group > Visual Basic.
- Select Insert > Module.
The Module window will appear. - Insert the following code in the Module window:
Option Explicit Sub deletemultiplesheets() Dim spreadsheet As Worksheet Application.DisplayAlerts = False For Each spreadsheet In Sheets If spreadsheet.Name <> ActiveSheet.Name Then spreadsheet.Delete End If Next spreadsheet Application.DisplayAlerts = True End Sub
- Click on the Run icon or press F5.
This will delete all the sheets except the active sheet.
Case 2: Delete Sheets With Specific Text String
In this example, we will remove every worksheet containing the string “Sales“. You can use your desired string instead of “Sales” in the code.
To delete all the sheets with a specific text string in Excel, follow the steps below:
- Press Alt+F11 to open the Microsoft Visual Basic for Applications window.
Or, go to the Developer tab > Code group > Visual Basic. - Select Insert > Module.
The Module window will appear. - Insert the following code in the Module window:
Option Explicit Sub DeleteSheetWithSameName() Dim spreadsheet As Worksheet Application.DisplayAlerts = False For Each spreadsheet In sheets ‘Replace Sales1 with the desired string If spreadsheet.Name Like "*" & "Sales" & "*" Then spreadsheet.Delete End If Next spreadsheet Application.DisplayAlerts = True End Sub
- Click on the Run icon or press F5.
This will delete all the sheets with a specific text string.
Practice Workbook
Download the following workbook and exercise.
Conclusion
Deleting multiple sheets in Excel is necessary to address issues such as redundancy, organization, storage, and security, and to prevent larger file sizes and improve indexing. This tutorial provides three methods for deleting multiple sheets: using the Ribbon Command, the Sheets Option, and the VBA code. Additionally, it explains how to delete sheets based on specific conditions using VBA code. If you have any questions regarding this topic, feel free to ask them in the comment section. Thank you!
Frequently Asked Questions
Can I recover deleted sheets in Excel?
No, you can not recover the deleted sheets in Excel. Deleting sheets in Excel is an irreversible process.
Are there any alternatives to deleting sheets in Excel?
Instead of deleting sheets, you can hide them if you want to temporarily remove them from view. Right-click on the sheet tab and choose “Hide“. Hidden sheets can be unhidden by right-clicking on any sheet name > Unhide > OK.
Can I delete sheets from a protected workbook or worksheet?
No, you cannot delete sheets from a protected workbook or worksheet unless you unprotect it first. Right-click on the sheet tab, choose “Unprotect Sheet” (if applicable), and then proceed with deletion.
Related Articles
- Shortcut to Delete Sheet in Excel
- How to Delete Hidden Sheets in Excel
- How to Delete All Sheets in Excel Except One
- How to Undo Delete Sheet in Excel
- [Fixed!] Delete Sheet Not Working in Excel
<< Go Back to Delete Sheet | Worksheets | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi, I have multiple excel files with few sheets in each file. I want to delete one particular sheet having same name in all files. is there any short cut please.
Hi SIVA!
You can check this article – https://www.exceldemy.com/delete-same-sheet-from-multiple-workbooks-in-excel-vba/
I hope it’ll be helpful for you.