VBA批量删除多个工作表完成后出现报错的原因及解决方法咨询

报错根本原因 #

你的报错核心是动态集合遍历过程中修改集合结构导致的引用失效：

• 你使用For Each ws In ThisWorkbook.Worksheets遍历Worksheets动态集合，执行ws.Delete删除工作表时，该集合的总元素数、元素索引会实时变更
• For Each迭代器无法适配这种动态变化，当迭代指针指向已被删除的工作表对象时，就会抛出对象引用无效的自动化错误，即使删除操作已经全部完成，依然会触发该报错。

正确解决方案 #

方案1：倒序索引遍历（推荐） #

从最后一个工作表倒序向前遍历，删除操作不会影响未遍历到的工作表索引，完全避免引用失效问题。

Option Explicit
Sub deletesheet()
    Dim wb As Workbook
    Dim i As Long
    Set wb = ThisWorkbook
    Application.DisplayAlerts = False
    
    ' 倒序遍历工作表索引
    For i = wb.Worksheets.Count To 1 Step -1
        If wb.Worksheets(i).Name <> "Sheet1" Then
            wb.Worksheets(i).Delete
        End If
    Next i
    
    Application.DisplayAlerts = True
End Sub

方案2：先收集待删对象再批量删除 #

先遍历所有工作表，将需要删除的工作表存入临时集合，遍历完原集合后再统一删除，全程不会在遍历原集合时修改其结构。

Option Explicit
Sub deletesheet()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim delSheets As Collection
    Set delSheets = New Collection
    Set wb = ThisWorkbook
    
    ' 第一步：收集待删除工作表
    For Each ws In wb.Worksheets
        If ws.Name <> "Sheet1" Then
            delSheets.Add ws
        End If
    Next ws
    
    ' 第二步：批量删除
    Application.DisplayAlerts = False
    For Each ws In delSheets
        ws.Delete
    Next ws
    Application.DisplayAlerts = True
End Sub

内容的提问来源于stack exchange，提问作者thangvc91