Excel VBA 删除Compiled工作表空白行报下标越界错误如何解决

报错根因 #

你反馈的Dim myWs As Worksheet行报错属于误判，下标越界实际触发在后续Set myWs = ThisWorkbook.Worksheets("Compiled")行，触发原因有两个：

• 原有代码依赖Select、ActiveSheet等依赖窗口焦点的操作，运行时如果窗口切换会导致Compiled工作表重命名失败，系统找不到对应名称的工作表就会报下标越界
• 原有复制逻辑本身存在问题：源数据是从第2行到lastRowSource，共lastRowSource -1行有效数据，但你设置的目标区域长度是lastRowSource行，多出来的位置自动留空，这是你出现大量空白行的核心原因

完整修复代码 #

Sub CombineData()
    Dim wsCompiled As Worksheet
    Dim ws As Worksheet
    Dim lastRowSource As Long, lastRowDest As Long
    Dim lastRowCompiled As Long
    Dim r As Long
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    ' 删除不需要的工作表，增加错误跳过避免不存在时报错
    On Error Resume Next
    ThisWorkbook.Sheets("Instructions").Delete
    ThisWorkbook.Sheets("TM Contacts").Delete
    On Error GoTo 0
    
    ' 新建Compiled工作表，先判断是否存在避免重名冲突
    On Error Resume Next
    Set wsCompiled = ThisWorkbook.Worksheets("Compiled")
    If Err.Number <> 0 Then
        Set wsCompiled = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsCompiled.Name = "Compiled"
    End If
    On Error GoTo 0
    
    ' 复制表头，无需选中操作
    ThisWorkbook.Worksheets("Lastname, First Name").Range("Table_1[#Headers]").Copy _
        Destination:=wsCompiled.Range("A1")
    
    ' 遍历复制所有非Compiled表的有效数据
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Compiled" Then
            lastRowSource = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            ' 跳过只有表头没有有效数据的工作表
            If lastRowSource >= 2 Then
                lastRowDest = wsCompiled.Cells(wsCompiled.Rows.Count, "A").End(xlUp).Row
                ' 修正复制区域长度，从根源避免多复制空白行
                ws.Range(ws.Cells(2, "A"), ws.Cells(lastRowSource, "AB")).Copy _
                    Destination:=wsCompiled.Cells(lastRowDest + 1, "A")
            End If
        End If
    Next ws
    
    ' 从下往上遍历删除完全空白行，避免从上往下删导致的漏删问题
    lastRowCompiled = wsCompiled.UsedRange.Rows(wsCompiled.UsedRange.Rows.Count).Row
    For r = lastRowCompiled To 2 Step -1
        ' 判断整行是否完全没有内容
        If WorksheetFunction.CountA(wsCompiled.Rows(r)) = 0 Then
            wsCompiled.Rows(r).Delete
        End If
    Next r
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

核心修改说明 #

• 全程用工作表对象直接操作，去掉所有依赖窗口状态的操作，彻底避免工作表引用错误
• 修正复制逻辑的区域长度计算，从根源减少不必要的空白行生成
• 增加工作表存在性判断，避免重名/创建失败导致的下标越界问题
• 空白行删除逻辑改用CountA判断整行是否完全空白，仅删除整行无任何内容的行，符合需求
• 从下往上遍历删除行，避免行号跳转导致的漏删问题

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