test for empty range 2

[Johnny42]

I'm setting FR as a range.
How can I test to see if the range is null ?

 

[Zathras]

Here is one way:

Sub DemoEmptyRange()
Dim r As Range
  Set r = Intersect(Range("A1:B2"), Range("C3:D4"))
  If r Is Nothing Then
    MsgBox "r is null"
  Else
    MsgBox r.Address
  End If
End Sub

 

[TonyJollans]

Hi Johnny,

Zathras is absolutely correct. Nothing is as close to Null as Excel gets. I suspect, given the title however, that you may want to check for the cells in the range all being empty - if so, here is one way ...

[blue]Set FR = Range("A1:X10") [green]' Perhaps[/green]
If Evaluate("=COUNTA(" & FR.Address & ")") = 0 Then
    MsgBox "Range is empty"
Else
    MsgBox "Range is not empty"
End If[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[