Sometimes you may need to delete multiple sheets in your workbook. For example, you may want to delete empty sheets or worksheets with outdated information.
In this tutorial, I will show you ways of deleting multiple sheets in Excel.
Caution: Make a workbook backup before deleting sheets because you cannot use the Undo command or the shortcut CTRL + Z to restore sheets you have deleted by mistake. However, you might be able to recover the sheets by closing the workbook without saving changes and reopening the file. If you delete worksheets and save the workbook the deleted sheets are gone forever.
Deleting Multiple Sheets in Excel
You can use various methods that apply Excel’s built-in features such as the Delete Sheet command on the Home tab to delete multiple sheets in a workbook.
In this section, I will show you how to delete multiple sheets in a workbook using Excel’s built-in features.
Method #1: Using the Delete Sheet Command in the Ribbon
Suppose you have a workbook with five sheets and want to delete all the sheets except the first one.
Note: Excel won’t let you delete all sheets in a workbook, at least one must remain. If you attempt to delete all sheets in a workbook Excel displays a warning message indicating that a workbook must contain at least one visible worksheet.
You can use the steps below to remove the sheets:
- Click the tab of Sheet2, press and hold down the Shift key, and click the tab of Sheet5.
Notice that sheets 2-5 tabs are highlighted in white indicating they are grouped.
Note: To select non-adjacent sheets, press and hold down the CTRL key and click the tabs of the sheets you want to delete.
- Click the Home tab, open the Delete drop-down list on the cells group, and select the Delete Sheet option.
Excel displays a warning message indicating that it will permanently delete the sheets and asks you to confirm whether you want to continue.
Note: Excel does not display this warning message if the sheets you want to delete are empty.
- Click Delete or press Enter to confirm deletion.
Excel deletes all the sheets you selected.
Method #2: Using the Delete Option in the Right-click Menu
Suppose you have a workbook with five sheets and want to delete all the sheets except the first one.
You can use the steps below to delete the sheets:
- Click the tab of Sheet2, press and hold down the Shift key, and click the tab of Sheet5.
Notice that sheets 2-5 tabs are highlighted in white indicating they are grouped.
Note: To select non-adjacent sheets, press and hold down the CTRL key and click the tabs of the sheets you want to delete.
- Right-click on the tab of any grouped sheet, then select Delete from the context menu.
Excel displays a warning message indicating that it will permanently delete the sheets and asks you to confirm whether you want to continue.
Note: Excel does not display this warning message if the sheets you want to delete are empty.
- Click Delete or press Enter to confirm deletion.
Excel deletes all the grouped sheets.
Method #3: Using a Keyboard Shortcut
Suppose you have a workbook with five sheets and want to delete all the sheets except the first one.
You can use the steps below to delete the sheets:
- Click the tab of Sheet2, press and hold down the Shift key, and click the tab of Sheet5.
Notice that sheets 2-5 tabs are highlighted in white indicating they are grouped.
Note: To select non-adjacent sheets, press and hold down the CTRL key and click the tabs of the sheets you want to delete.
- Press ALT, H, D, and S in sequence (Press the ALT key and release it, press the H key and release it, press the D key and release it, and press the S key and release it).
Excel displays a warning message indicating that it will permanently delete the sheets and asks you to confirm whether you want to continue.
Note: Excel does not display this warning message if the sheets you want to delete are empty.
- Click Delete or press Enter to confirm deletion.
Excel deletes all the grouped sheets.
Also read: Download One Sheet From Excel
Delete Multiple Sheets in Excel Using VBA
With VBA code, you can delete multiple sheets in a workbook with greater flexibility and control.
For instance, VBA allows you to delete sheets based on specific criteria, such as sheet names or tab colors.
In this section, I will show you various scenarios for deleting multiple sheets in a workbook using VBA code.
Scenario #1: Delete Sheets Except the Active Sheet
Suppose you have a workbook with five sheets and Sheet2 is the active sheet. You want to delete all sheets except the active sheet.
You can use the VBA code below to do the job.
Sub DeleteSheetsExceptActive()
Application.DisplayAlerts = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = True
End Sub
When you run the code it deletes all sheets in the workbook except the active one.
The code iterates through each sheet in the workbook, deleting it if it is not the active sheet. At the beginning, it disables the delete confirmation dialog box allowing uninterrupted deletion of sheets, and turns it back on at the end of the operation.
Scenario #2: Delete Sheets Containing a Specific Substring in the Name (Substring Hardcoded)
Suppose you have a workbook with four sheets. You want to delete all sheets containing ‘Temp’ in their name.
You can use the VBA code below to do the task.
Sub DeleteSheetWithSubtring()
Application.DisplayAlerts = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "*" & "Temp" & "*" Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = True
End Sub
When you run the code it deletes all sheets containing ‘Temp’ in the name.
The code iterates through each sheet in the workbook, deleting it if it contains ‘Temp’ in its name.
At the beginning, it turns off the delete confirmation dialog box allowing uninterrupted deletion of sheets, and enables it at the end of the operation.
Scenario #3: Delete Sheets Containing a Specific Substring in Name Based on User Input
Suppose you have a workbook with four sheets. You want to delete all sheets containing ‘Temp’ in their name based on user input.
You can use the VBA code below to do the task.
Sub DeleteSheetsUserInput()
Dim ws As Worksheet
Dim sheetName As String
Dim substring As String
Dim confirmDelete As VbMsgBoxResult
substring = InputBox("Enter the substring to search for in sheet names:")
confirmDelete = MsgBox( _
"Are you sure you want to delete sheets containing the substring """ & substring & """?", _
vbYesNo + vbQuestion, "Confirm Deletion")
If confirmDelete = vbNo Then
Exit Sub
End If
For Each ws In ThisWorkbook.Worksheets
If InStr(1, ws.Name, substring, vbTextCompare) > 0 Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
MsgBox "Sheets containing the substring """ & substring & """ have been deleted."
End Sub
When you execute the code it first prompts you to enter the substring to search for in worksheet names.
In this case, I have entered ‘Temp.’
When you click OK or press Enter the code asks you to confirm you want to delete sheets containing the substring you provided.
When you click Yes or press Enter the code deletes all the target sheets.
The code displays a message indicating that it has completed the task.
Scenario #4: Delete Sheets With a Specific Tab Color
Suppose you have a workbook with four sheets: two have red tab color, and two have green. You want to delete all the sheets with red tab color.
You can use the VBA code below to do the task.
Sub DeleteSheetsWithRedTabs()
Application.DisplayAlerts = False
Dim ws As Worksheet
Dim redColor As Long
redColor = RGB(255, 0, 0)
For Each ws In ThisWorkbook.Worksheets
If ws.Tab.Color = redColor Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = True
End Sub
When you run the code it deletes all sheets with red tabs.
The code loops through each sheet in the workbook, deleting it if its tab is red. At the beginning, it turns off the delete confirmation dialog box allowing uninterrupted deletion of sheets, and enables it at the end of the operation.
Note: You can change the tab color of a worksheet by right-clicking the tab, hovering over the Tab Color option on the shortcut menu, and selecting a color on the submenu.
Scenario #5: Delete Sheets With a Specific Cell Content
Suppose you have a workbook with four sheets. You want to delete all sheets containing the word ‘Delete’ in cell A1.
In this case, only sheets ‘Jan Report_Temp’ and ‘Feb Report_Temp’ have the word ‘Delete’ in cell A1.
You can use the VBA code below to do the task.
Sub DeleteSheetsWithDeleteInA1()
Application.DisplayAlerts = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Range("A1").Value = "Delete" Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = True
End Sub
When you run the code it deletes all sheets containing ‘Delete’ in cell A1.
The code iterates through each sheet in the workbook, deleting it if it contains ‘Delete’ in cell A1.
At the beginning, it turns off the delete confirmation dialog box allowing uninterrupted deletion of sheets, and enables it at the end of the process.
Also read: Copy Multiple Sheets to New Workbook in Excel
Precautions to Take Before Deleting Sheets
When you delete sheets in a workbook and save the file, the deleted sheets are gone forever. It is therefore critical to take the following precautions before deleting sheets in a workbook:
- Make a backup of the workbook.
- Review the sheets to ensure they don’t contain critical information, formulas, or values needed for analysis later.
- Look for any formulas in other sheets referencing data on the sheets you want to delete. Deleting sheets with referenced data can cause errors in dependent cells or formulas.
- If there are hidden sheets in the workbook, unhide them to confirm you’re not unintentionally deleting something important.
- If it’s a shared workbook, check with others who use it to make sure the sheets you want to delete aren’t needed for their work.
I have shown you ways to delete sheets in a workbook using Excel’s built-in features and VBA. I hope you found the tutorial helpful.
Other Excel articles you may also like: