In Microsoft Excel, we can delete multiple sheets with different easy approaches. We can apply options from the Context Menu or Excel ribbons, and sometimes we can also insert VBA codes to meet our requirements. In this article, you’ll get to learn all the suitable methods to delete multiple sheets in Excel with appropriate examples and illustrations.
Download the following workbook and exercise.
4 Easy Ways to Delete Multiple Sheets in Excel
1. Use Ribbon Option to Delete Multiple Sheets in Excel
From the Ribbon option, we can delete multiple sheets.
- Select the Sheets we want to delete by pressing and holding the Shift key.
- Now go to the Home tab and select Delete > Delete Sheet.
- A dialogue box will open.
- Click OK.
- Then we can see that the selected sheets are deleted.
2. Use Sheet Option to Delete Multiple Sheets in Excel
2.1 For Adjacent Worksheet
We need to follow the steps below to delete multiple worksheets that are adjacent.
- By pressing and holding the Shift key, select the first sheet and the last one with the mouse we want to delete.
- Right-click your mouse button on the Sheet tab and select Delete.
- Finally, the sheets are deleted.
2.2 For Non-Adjacent Worksheet
We can also delete the non-adjacent worksheets.
- Select the cells we want to delete by pressing the Ctrl key.
- Now on the Sheet tab, Right-click on the mouse and select Delete.
- A dialogue box pops up.
- Click OK and see the result.
3. Delete Multiple Worksheets by Hybrid Keyboard
Deleting multiple worksheets in Excel by pressing the keyboard is one of the easiest and fastest ways. We just need to select the sheets, Right-click on the Sheet tab, and press D from the keyboard. The sheets will be deleted.
4. Insert VBA Codes to Delete Multiple Excel Sheets
4.1 Delete All Sheets Keeping the Active Sheet
VBA is one of the most suitable methods to delete all sheets except the active sheet.
- From the sheet tab, select the active sheet, Right-click on the mouse, and select View Code.
- Now copy the following codes and paste them into your VBA module.
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
- Hit the Run option and we’ll see that all the sheets are deleted except the active one.
Read More: How to Delete Excel Sheet Using VBA (10 VBA Macros)
4.2 Deleting Sheets with Specific Text String
We can easily delete all the sheets with a specific text string.
- Select the sheet from the sheet tab.
- Now Right-click on the mouse and select View Code.
- Then copy the following codes and paste them into your VBA module. And click on the Run option.
Option Explicit Sub DeleteSheetWithSameName() Dim spreadsheet As Worksheet Application.DisplayAlerts = False For Each spreadsheet In sheets If spreadsheet.Name Like "*" & "Sales1" & "*" Then MsgBox spreadsheet.Name spreadsheet.Delete End If Next spreadsheet Application.DisplayAlerts = True End Sub
- A dialogue box will pop up for the confirmation and select OK.
- Finally, we can see the selected sheets with the selected text strings are deleted.
By following these methods, we can easily delete multiple sheets in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.
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.
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.