Define an empty "range" after deleting it.

Question

I work with ranges, and sometimes all cells in this object are deleted ( Range.Cells.Delete)

I played and found:

• after deleting all cells the variable type is still RangeandIs Not Nothing
• a call to any of its members leads to an error "Required object"

Here is my small subgroup to check it out:

Sub test()
    Dim r As Range

    Debug.Print r Is Nothing        ' -> true
    Set r = ActiveSheet.Range("a2")
    Debug.Print TypeName(r)         ' -> Range
    r.Cells.Delete shift:=xlShiftUp
    On Error GoTo ErrHandler
    Debug.Print r Is Nothing        ' -> False
    Debug.Print TypeName(r)         ' -> Range
    Debug.Print r.Address           ' -> "Err: Object required"
    Debug.Print r.Cells.Count       ' -> "Err: Object required"

    Exit Sub
ErrHandler:
    MsgBox "Err: " & Err.Description
    Resume Next
End Sub

My question is: how can I determine (hopefully without error handling) whether the object is empty Range?

The shortest option I've found so far:

Function IsEmptyRange(testRange As Range) As Boolean
    If TypeName(testRange) <> "Range" Then
        IsEmptyRange = False
    Else
        On Error GoTo EmptyRange
        IsEmptyRange = testRange.Cells.Count = 0
        If False Then
EmptyRange:
            Err.Clear
            IsEmptyRange = True
        End If
    End If
End Function

Answer 1

Daily Dose of Excel, question. DDoE "", " ". SO (by @DougGlancy), (, , If False Then...).

, , , Nothing, . .

Sub test()
    Dim r As Range

    Debug.Print r Is Nothing        ' -> true
    Set r = ActiveSheet.Range("a2")
    Debug.Print TypeName(r)         ' -> Range
    r.Cells.Delete shift:=xlShiftUp

    Set r = Nothing                 ' -> end of problem

    On Error GoTo ErrHandler

    Debug.Print r Is Nothing        ' -> True

    If Not r Is Nothing Then
        Debug.Print TypeName(r)         ' -> Range
        Debug.Print r.Address           ' -> "Err: Object required"
        Debug.Print r.Cells.Count       ' -> "Err: Object required"
    End If

    Exit Sub
ErrHandler:
    MsgBox "Err: " & Err.Description
    Resume Next
End Sub

, , (.. r Sub), .