[Excel VBA] How to Delete All Sheets Except One Specific Sheet

2025年12月26日

There are many situations where you want to clear all report sheets created during a previous process before running a macro again, while keeping a specific “Template” or “Master” sheet intact.

When deleting multiple sheets in VBA, the most important thing to pay attention to is the loop order. This article explains how to write reliable VBA code to safely keep one specific sheet while efficiently deleting all others.

The Golden Rule: Loop “Backwards” When Deleting

When deleting multiple items from a collection, it is a golden rule to run the For loop from back to front (e.g., 10th → 9th → …).

If you try to delete in order from the front (1st → 2nd → …), a problem occurs: the moment you delete the 2nd sheet, the sheet that was previously 3rd shifts up to become the new 2nd sheet. However, since the loop counter advances to 3, the “new” 2nd sheet is skipped and not processed.

By using For i = Worksheets.Count To 2 Step -1, you loop backwards. This eliminates the need to worry about index shifting, allowing you to safely delete all target sheets.

Complete VBA Code

The following code keeps only one sheet named “Master” and deletes all other worksheets.

VBA

Sub DeleteAllSheetsExceptOne()

    ' Declare variables
    Dim sheetToKeep As Worksheet
    Dim i As Long
    
    ' --- 1. Identify the sheet to keep ---
    On Error Resume Next ' Avoid error if the sheet does not exist
    Set sheetToKeep = ThisWorkbook.Worksheets("Master")
    On Error GoTo 0
    
    If sheetToKeep Is Nothing Then
        MsgBox "The sheet 'Master' was not found.", vbCritical
        Exit Sub
    End If
    
    ' --- 2. Exit if there is only one sheet ---
    ' Excel requires at least one visible sheet to exist
    If ThisWorkbook.Worksheets.Count <= 1 Then
        MsgBox "There is only one sheet, so no deletion occurred.", vbInformation
        Exit Sub
    End If

    ' --- 3. Disable alerts and execute deletion ---
    Application.DisplayAlerts = False
    
    ' Move the sheet to keep to the far left (Index 1)
    sheetToKeep.Move Before:=ThisWorkbook.Worksheets(1)
    
    ' Loop from the last sheet down to the 2nd sheet and delete
    For i = ThisWorkbook.Worksheets.Count To 2 Step -1
        ThisWorkbook.Worksheets(i).Delete
    Next i
    
    ' --- 4. Re-enable alerts (Crucial) ---
    Application.DisplayAlerts = True
    
    MsgBox "All sheets except '" & sheetToKeep.Name & "' have been deleted."

End Sub

Explanation of Key Points

1. Move the Sheet to Keep to the Front

sheetToKeep.Move Before:=ThisWorkbook.Worksheets(1)

Before starting the deletion loop, we use the .Move method to move the sheet we want to keep to the far left of the workbook (Index position 1). By doing this, the subsequent loop logic becomes very simple: “Delete everything from the 2nd sheet to the end.”

2. Hide Warning Messages

Application.DisplayAlerts = False

The .Delete method for sheets normally displays a warning message (“Data may exist in the sheet selected for deletion…”), which pauses the macro. By declaring Application.DisplayAlerts = False, you suppress these warnings, allowing the process to run smoothly without user intervention.

3. Loop Backwards to Delete

For i = ThisWorkbook.Worksheets.Count To 2 Step -1
    ThisWorkbook.Worksheets(i).Delete
Next i

This is the core of safe deletion.

For i = Worksheets.Count To 2: The loop counter i starts from the total number of sheets and repeats until it reaches 2.
Step -1: This tells the loop to decrease the counter by 1 in each iteration.

This ensures that sheets are deleted starting from the far right, moving leftwards, stopping just before the 1st sheet (the one we moved to the start).

4. Restore Warning Messages

Application.DisplayAlerts = True

It is extremely important to set DisplayAlerts back to True after the process finishes. If you forget this, Excel will stop showing any warnings (even for manual user actions) after the macro finishes, which can be dangerous.