Using Nothing and Null

[Mikhail]

What should be used with range variables if they are empty - IsEmpty(r) or r
is Nothing? Provided that r is declared as Range?
What is the best way to check if range contains no cells, for example list
on worksheet contains only coulumns headers?
Currently I use both techniques - either Set r = Nothing and later test if r
Is Nothing then...or r = Null and later test if IsEmpty(r) then...
Thanks
Mike510

 

[Dave Peterson]

If r is a multicell range, then you won't want to use isempty(). That refers to
a single cell.

dim r as range
set r = activesheet.range("a1:b3")

if application.counta(r) = 0 then
'all empty
else
'not all empty
end if

And checking to see if a range is nothing usually means that it hasn't been
assigned a range yet or you've tried to assign it to something that doesn't
exist.

Dim r1 As Range
Dim r2 As Range

Set r1 = ActiveSheet.Range("a1:b3")
On Error Resume Next
Set r2 = r1.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If r2 Is Nothing Then
MsgBox "no formulas in " & r1.Address
Else
MsgBox "formulas found in: " & r2.Address
End If