Test if range exists in VBA

excel vba range offset
Colten J Nye picture Colten J Nye · Sep 27, 2012 · Viewed 53.2k times · Source

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."

Answer

Steztric picture Steztric · Oct 4, 2013

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

Related questions

Creating an Array from a Range in VBA

I'm having a seemingly basic problem but can't find any resources addressing it. Simply put, I just want to load the contents of a Range of cells (all one column) into an Array. I am able to accomplish this by …

Read More
Detect if range is empty

I want to check if a range in Excel is empty. How do I write in VBA code: If Range("A38":"P38") is empty

Read More
VBA paste range

I have simple goal of copying range and pasting it into another spreadsheet. The following code below gives copies, but does not paste. Sub Normalize() Dim Ticker As Range Sheets("Sheet1").Activate Set Ticker = Range(Cells(2, 1), Cells(65, 1)) Ticker.Copy Sheets("…

Read More