Excel VBA删除自定义工作表时触发"subscript out of range"错误排查

The core issue with your deletesheetsspecific macro is that you’re looping forwards through the sheets while deleting them. Here’s why this causes problems:

• When you delete a sheet, the total number of sheets decreases, and the remaining sheets shift left in their index positions. For example, if you delete sheet 3, sheet 4 becomes the new sheet 3.
• Your loop keeps incrementing j regardless, so you skip some sheets and eventually try to access a sheet index that no longer exists (triggering the "subscript out of range" error).

Corrected Macro Code #

Here’s a fixed version that resolves this issue, plus a more robust check to ensure you only keep your original tab 1 and tab 2 sheets:

Sub deletesheetsspecific()
    Dim j As Integer
    Application.DisplayAlerts = False
    
    ' Loop BACKWARDS from the last sheet to the first
    For j = ActiveWorkbook.Sheets.Count To 1 Step -1
        Dim sheetName As String
        sheetName = Sheets(j).Name
        
        ' Only delete sheets that are NOT "tab 1" or "tab 2"
        If sheetName <> "tab 1" And sheetName <> "tab 2" Then
            ActiveWorkbook.Sheets(j).Delete
        End If
    Next j
    
    Application.DisplayAlerts = True
End Sub

Key Improvements Explained #

1. Backwards Looping: By starting from the last sheet (Sheets.Count) and moving down to 1, deleting a sheet doesn’t affect the indices of the sheets you haven’t checked yet. This ensures every sheet is evaluated, and you never try to access a non-existent index.

2. Precise Sheet Check: Instead of relying on the first 3 characters, we explicitly check if the sheet name is exactly tab 1 or tab 2. This avoids accidental retention of sheets like tab3 or tabXYZ that would pass the left-3 check but aren’t your original tabs.

3. Readable Variable Names: Renamed k to sheetName for clarity—descriptive variable names make your code easier to debug and maintain later.

Optional Extra Safety Measures #

• Prevent Accidental Deletion of Original Tabs: Add this line right after the For j = ... line to stop the loop if only your two original tabs remain:

  If ActiveWorkbook.Sheets.Count <= 2 Then Exit For
  

• Handle Protected Sheets: If some sheets might be protected and can’t be deleted, add error handling to skip them without crashing the macro:

  On Error Resume Next
  ActiveWorkbook.Sheets(j).Delete
  On Error GoTo 0
  

  Replace the existing Delete line with these three lines.

内容的提问来源于stack exchange，提问作者P. MAJ