VBA脚本Range Error：多工作表移除指定课程时触发范围错误

你的代码触发Range Error的核心原因通常是这几点：

• 目标工作簿未打开或名称拼写错误（比如漏加文件后缀）
• deactivated表格无数据，导致DataBodyRange返回Nothing，后续遍历空范围报错
• 目标工作表名称与实际不符，无法定位有效工作表
• 原代码用UsedRange覆盖全表，而非需求指定的H-T列，可能意外访问无效单元格

以下是修复并优化后的代码，严格匹配「仅处理H-T列」的需求：

Sub RemoveDeactivatedCourses()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim deactivatedTable As ListObject
    Dim removalWords As Variant
    Dim targetRange As Range
    Dim sheetNames As Variant
    Dim i As Long, j As Long
    Dim cellValue As String, word As Variant
    
    ' 定位目标工作簿（避免硬编码名称出错）
    On Error Resume Next
    Set wb = Workbooks("Updated Masterv2.xlsx") ' 根据实际文件后缀调整，比如.xlsm
    On Error GoTo 0
    If wb Is Nothing Then
        MsgBox "未找到工作簿Updated Masterv2，请确认文件已打开", vbExclamation
        Exit Sub
    End If
    
    ' 定位停用课程表格
    Set deactivatedTable = wb.Sheets("Sheet2").ListObjects("deactivated")
    If deactivatedTable.DataBodyRange Is Nothing Then
        MsgBox "deactivated表格中没有数据", vbExclamation
        Exit Sub
    End If
    ' 将移除词存入数组，提升处理效率
    removalWords = deactivatedTable.ListColumns(1).DataBodyRange.Value
    
    ' 待处理工作表列表
    sheetNames = Array("SXF", "SFN", "FAR", "FGN", "BIS", "BEM", "Other", "LRH")
    
    ' 遍历每个目标工作表
    For i = LBound(sheetNames) To UBound(sheetNames)
        On Error Resume Next
        Set ws = wb.Sheets(sheetNames(i))
        On Error GoTo 0
        If ws Is Nothing Then
            MsgBox "未找到工作表" & sheetNames(i) & "，跳过该表", vbExclamation
            Set ws = Nothing
            GoTo NextSheet
        End If
        
        ' 严格定位H-T列的已使用区域
        With ws
            Set targetRange = Intersect(.UsedRange, .Range("H:T"))
        End With
        If targetRange Is Nothing Then
            MsgBox ws.Name & "的H-T列无数据，跳过", vbInformation
            GoTo NextSheet
        End If
        
        ' 批量处理单元格内容
        For Each cell In targetRange
            cellValue = cell.Value
            If cellValue <> "" Then
                For Each word In removalWords
                    cellValue = Replace(cellValue, CStr(word), "")
                Next word
                cell.Value = cellValue
            End If
        Next cell
        
NextSheet:
        Set ws = Nothing
    Next i
    
    MsgBox "指定工作表的H-T列已完成停用课程移除", vbInformation
End Sub

关键修改说明 #

• 存在性检查：提前验证工作簿、工作表、表格数据是否存在，避免对象引用错误
• 范围精准限定：用Intersect(.UsedRange, .Range("H:T"))严格锁定需求区域，减少无效操作
• 效率优化：将移除词存入数组，比逐个遍历Range的处理速度提升数倍
• 容错处理：跳过不存在的工作表，保证流程不中断

内容的提问来源于stack exchange，提问作者Jordan Parr-Hess