Isempty, Nothing, Null...for optional range object?

[MikeAshton55Mad]

I have a function where I am passing an optional Range object and trying to check whether or not the function is being passed a range of data.

I've tried cells.count, but if the Range object doesn't exist Excel doesn't like that; I've tried If TheRange=Nothing. I've tried ISEMPTY(TheRange). Nothing seems to work, and I can't find anything that is designed for ranges. What's the trick?

Thanks,

Mike

 

[sweater_vests_rock]

Mike.

Drop these two routines in an empty worksheet and try them out.

'This sub checks if the range has been set.
Sub RangeSet()

Dim TheRange As Range

If Not TheRange Is Nothing Then
Else
    MsgBox "Before Setting the Range"
    Set TheRange = Range("A1")
End If
    
If Not TheRange Is Nothing Then
    MsgBox "After the range is set.  TheRange = " & TheRange.Address
End If

End Sub

'This sub checks to see if an existing range contains any data.
Sub RangeEmpty()

Dim TheRangeNonEmpty As Range, TheRangeEmpty As Range
Dim loop_break As Boolean
Dim rw As Long, col As Integer, rwCtr As Long, colCtr As Integer

wsName = ActiveSheet.Name

Worksheets(wsName).Cells(1, 1) = "Not Empty"

Set TheRangeNonEmpty = Range("A1:A2")
Set TheRangeEmpty = Range("b1:b2")

loop_break = False
rw = TheRangeEmpty.Rows.Count
col = TheRangeEmpty.Columns.Count

rwCtr = 0
colCtr = 0

Do
    rwCtr = rwCtr + 1
    Do
        colCtr = colCtr + 1
        If TheRangeEmpty.Cells(rwCtr, colCtr) <> "" Then
            loop_break = True
        End If
    Loop While loop_break = False And colCtr < col
Loop While loop_break = False And rwCtr < rw

TheRangeEmpty.Activate

If rwCtr = rw And colCtr = col Then
    MsgBox "TheRangeEmpty is empty"
Else
    MsgBox "TheRangeEmpty is non-empty"
End If

rwCtr = 0
colCtr = 0
Do
    rwCtr = rwCtr + 1
    Do
        colCtr = colCtr + 1
        If TheRangeNonEmpty.Cells(rwCtr, colCtr) <> "" Then
            loop_break = True
        End If
    Loop While loop_break = False And colCtr < col
Loop While loop_break = False And rwCtr < rw

TheRangeNonEmpty.Activate

If rwCtr = rw And colCtr = col Then
    MsgBox "TheRangeNonEmpty is empty"
Else
    MsgBox "TheRangeNonEmpty is non-empty"
End If

End Sub

Hope that helps. Ben.

 

[MikeAshton55Mad]

Sweet - Thanks Ben (sweater vests rock?). It was something as simple as saying ThisRange is Nothing rather than If ThisRange=Nothing. Never would have figured that out without your help, and I learned a few other things besides. Thanks!

Mike