Test if range exists in VBA

calendar_today Asked Sep 26, 2012
thumb_up 14 upvotes
history Updated April 16, 2026

Question posted 2012 · +5 upvotes

I have a dynamically defined named range in my excel ss that grabs data out of a table based on a start date and an end date like this

=OFFSET(Time!$A$1,IFERROR(MATCH(Date_Range_Start,AllDates,0)-1,MATCH(Date_Range_Start,AllDates)),1,MATCH(Date_Range_End,AllDates)-IFERROR(MATCH(Date_Range_Start,AllDates,0)-1,MATCH(Date_Range_Start,AllDates)),4)

But if the date range has no data in the table, the range doesn’t exists (or something, idk). How can I write code in VBA to test if this range exists or not?

I have tried something like

If Not Range("DateRangeData") Is Nothing Then

but I get “Runtime error 1004, method ‘Range’ of object ‘_Global’ failed.”

Accepted answer +14 upvotes

Here is a function I knocked up to return whether a named range exists. It might help you out.

Function RangeExists(R As String) As Boolean
    Dim Test As Range
    On Error Resume Next
    Set Test = ActiveSheet.Range(R)
    RangeExists = Err.Number = 0
End Function

VBA Core objects referenced (4)

Top vba Q&A (6)

+14 upvotes ranks this answer #28 out of 81 vba solutions on this site .
vba