如何解决VBA循环MW开头工作表时出现的Error 424错误？

我编写了一段VBA代码，核心逻辑是循环处理所有名称以MW开头的工作表，执行列删除、数据运算及列名修改操作。原本代码可正常运行，但添加工作表循环后，在If Not Rng Is Nothing Then Rng.EntireColumn.Delete语句处出现**Error 424（需要对象）**错误。我猜测问题出在工作表循环逻辑上，可能是重复处理导致代码无法正常执行。代码如下：

Dim Cl As Range, Rng As Range
    Dim Cl2 As Range, Rng2 As Range
    Dim Cl3 As Range, Rng3 As Range
    Dim c As Range
    Dim Cl4 As Range, Rng4 As Range
    Dim Lastrow As Long
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name Like "MW*" Then

            For Each Cl In Range("A1:J1")
                Select Case Cl.Value
                    Case "#", "Coupler Detached", "Coupler Attached", "Host Connected", "End Of File", "ms"
                        If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
                End Select
            Next Cl
            If Not Rng Is Nothing Then Rng.EntireColumn.Delete

            For Each Cl4 In Range("D1")
                Select Case Cl4.Value
                    Case "Abs Pres (kPa) c:1 2"
                        If Rng4 Is Nothing Then Set Rng4 = Cl4 Else Set Rng4 = Union(Rng4, Cl4)
                End Select
            Next Cl4
            If Not Rng4 Is Nothing Then
                Application.ScreenUpdating = False

                Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
                For Each c In Range("D2:D" & Lastrow)
                    c.Value = c.Value * 0.101972
                Next
                Application.ScreenUpdating = True

            End If


            For Each Cl2 In Range("A1:J1")
                Select Case Cl2.Value
                    Case "Abs Pres (kPa) c:1 2"
                        If Rng2 Is Nothing Then Set Rng2 = Cl2 Else Set Rng = Union(Rng, Cl2)
                End Select
            Next Cl2
            If Not Rng2 Is Nothing Then Rng2.Value = ("LEVEL")


            For Each Cl3 In Range("A1:J1")
                Select Case Cl3.Value
                    Case "Temp (°C) c:2"
                        If Rng3 Is Nothing Then Set Rng3 = Cl3 Else Set Rng = Union(Rng, Cl3)
                End Select
            Next Cl3
            If Not Rng3 Is Nothing Then Rng3.Value = ("TEMPERATURE")

        End If
    Next ws

Error 424的核心原因有三点：

• 未限定工作表对象：所有Range、Cells调用未指定所属的ws对象，默认引用当前活动工作表，循环切换工作表时会导致对象引用混乱，甚至指向已被操作的无效对象。
• Range变量未重置：Rng、Rng2等变量在处理完一个工作表后未重置为Nothing，下一次循环会携带上一个工作表的无效引用，导致操作失败。
• 代码笔误：处理Rng2、Rng3的Union操作时，错误地将目标变量写成了Rng，导致变量引用混乱。

以下是修复后的代码：

Dim Cl As Range, Rng As Range
Dim Cl2 As Range, Rng2 As Range
Dim Cl3 As Range, Rng3 As Range
Dim c As Range
Dim Cl4 As Range, Rng4 As Range
Dim Lastrow As Long
Dim ws As Worksheet

' 提前关闭屏幕更新，提升整体运行效率
Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "MW*" Then
        ' 处理新工作表前，重置所有Range变量
        Set Rng = Nothing
        Set Rng2 = Nothing
        Set Rng3 = Nothing
        Set Rng4 = Nothing
        
        ' 1. 删除指定列：明确限定操作当前工作表的Range
        For Each Cl In ws.Range("A1:J1")
            Select Case Cl.Value
                Case "#", "Coupler Detached", "Coupler Attached", "Host Connected", "End Of File", "ms"
                    If Rng Is Nothing Then
                        Set Rng = Cl
                    Else
                        Set Rng = Union(Rng, Cl)
                    End If
            End Select
        Next Cl
        If Not Rng Is Nothing Then Rng.EntireColumn.Delete
        
        ' 2. 数据运算：限定操作当前工作表的Cells和Range
        For Each Cl4 In ws.Range("D1")
            Select Case Cl4.Value
                Case "Abs Pres (kPa) c:1 2"
                    Set Rng4 = Cl4 ' 单个单元格无需Union，直接赋值
            End Select
        Next Cl4
        If Not Rng4 Is Nothing Then
            Lastrow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
            For Each c In ws.Range("D2:D" & Lastrow)
                c.Value = c.Value * 0.101972
            Next c
        End If
        
        ' 3. 修改列名：修正笔误，限定当前工作表Range
        For Each Cl2 In ws.Range("A1:J1")
            Select Case Cl2.Value
                Case "Abs Pres (kPa) c:1 2"
                    Set Rng2 = Cl2
            End Select
        Next Cl2
        If Not Rng2 Is Nothing Then Rng2.Value = "LEVEL"
        
        ' 4. 修改温度列名：修正笔误，限定当前工作表Range
        For Each Cl3 In ws.Range("A1:J1")
            Select Case Cl3.Value
                Case "Temp (°C) c:2"
                    Set Rng3 = Cl3
            End Select
        Next Cl3
        If Not Rng3 Is Nothing Then Rng3.Value = "TEMPERATURE"
    End If
Next ws

Application.ScreenUpdating = True

修复要点说明：

1. 所有Range、Cells调用添加ws.前缀，明确指定操作当前循环的工作表，避免引用活动表导致的混乱。
2. 每次进入新工作表处理时，重置所有Rng系列变量为Nothing，清除上一个工作表的无效引用。
3. 修正Rng2、Rng3循环中的Union笔误，针对单个单元格匹配场景直接赋值，简化代码。
4. 将屏幕更新开关移到循环外，减少重复开关的性能损耗。

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