How can I stop my vba code from giving me an error 424

Question

I've created some code that can perform a lot of things (with a lot of help from you guys, thanks). In essence, it performs a loop through each worksheet beginning with MW, deleting some columns, performing some procedures, and changing some column names. The code is now working, but when I add the loop through the spreadsheets, I get an error 424 on the line that says, "If not Rng Is Nothing Then Rng.EntireColumn.Delete." How can I fix this?
My theory is that because my ws loop doesn't function properly, the code cannot run because the sheet has already been processed.

Here's my code

    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

Answer 1

Object Variables in Loops

• The main issue was that you cannot combine cells with an invalid range so you need to 'reset' the rng* variables i.e. explicitly set them to nothing. For example, in the first iteration, there were cells combined into rng. Now you delete rng and the variable's state is still Nothing although you have deleted the range making it invalid. In the next iteration, you try to combine this invalid range (which is Nothing) with a matching cell via Union so the error occurs. BTW, even if you didn't delete the range, again, an error would occur because you cannot combine ranges from different worksheets.
• In the following code, pay attention to how this (Set rng = Nothing) is done after each stage per worksheet when only a single rng variable is used.
• Also, note how ws and cell are 'safe', they don't need to be reset, since For Each... could be translated as something like Set ws = WhatEverWorksheet or Set cell = WhatEverCell.

Sub ALot()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' If it's not, change back to 'ActiveWorkbook'.
   
    Application.ScreenUpdating = False
    
    Dim ws As Worksheet, rng As Range, cell As Range

    For Each ws In wb.Worksheets
        
        If ws.Name Like "MW*" Then
            
            ' Delete columns.
            For Each cell In ws.Range("A1:J1").Cells
                Select Case CStr(cell.Value)
                    Case "#", "Coupler Detached", "Coupler Attached", _
                            "Host Connected", "End Of File", "ms"
                        If rng Is Nothing Then Set rng = cell Else Set rng = Union(rng, cell)
                End Select
            Next cell
            If Not rng Is Nothing Then
                rng.EntireColumn.Delete
                Set rng = Nothing ' reset
            End If
            
            ' Check 4th column.
            Set cell = ws.Range("D1")
            If CStr(cell.Value) = "Abs Pres (kPa) c:1 2" Then
                Set rng = ws.Range("D2", ws.Cells(ws.Rows.Count, "D").End(xlUp))
                rng.Value = ws.Evaluate(rng.Address & "*0.101972")
                Set rng = Nothing ' reset
            End If

            ' Check "Abs Pres (kPa) c:1 2".
            For Each cell In ws.Range("A1:J1").Cells
                Select Case CStr(cell.Value)
                    Case "Abs Pres (kPa) c:1 2"
                        If rng Is Nothing Then Set rng = cell Else Set rng = Union(rng, cell)
                End Select
            Next cell
            If Not rng Is Nothing Then
                rng.Value = "LEVEL"
                Set rng = Nothing ' reset
            End If
             
            ' Check "Temp (°C) c:2".
            For Each cell In ws.Range("A1:J1").Cells
                Select Case cell.Value
                    Case "Temp (°C) c:2"
                        If rng Is Nothing Then Set rng = cell Else Set rng = Union(rng, cell)
                End Select
            Next cell
            If Not rng Is Nothing Then
                rng.Value = "TEMPERATURE"
                Set rng = Nothing ' reset
            End If

        'Else ' is not like "MW*"; do nothing
        End If
    
    Next ws

    Application.ScreenUpdating = True

    MsgBox "Worksheets processed.", vbInformation

End Sub