Check if range exists in VBA

Question

I have a dynamically defined named range in my excel ss that captures data from a table based on a start date and an end date such as

=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 there is no data in the table in the date range, the range does not exist (or something, idk). How can I write code in VBA to check if this range exists or not?

I tried something like

 If Not Range("DateRangeData") Is Nothing Then 

but I get "Runtime Error 1004, the Range method of the _Global object failed."

Answer 1

You can replicate the match in your VBA to count before using the range how many rows you would have, or you can use error handling:

 On Error Resume Next Debug.Print range("DateRangeData").Rows.Count If Err = 1004 Then MsgBox "Range Empty" Exit Sub Else MsgBox "Range full" End If Err.Clear On Error GoTo 0 

Answer 2

Here is the function that I knocked to return if a named range exists. It can help you.

 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 

Answer 3

This is a different approach. This has the advantage of taking the container and the name you want to verify. This means that you can test either sheet names or book names, for example.

Like this:

 If NamedRangeExists(ActiveSheet.Names, "Date") Then ... Else ... End If 

or

 If NamedRangeExists(ActiveWorkbook.Names, "Date") Then ... Else ... End If 

---

 Public Function NamedRangeExists(ByRef Container As Object, item As String) As Boolean Dim obj As Object Dim value As Variant On Error GoTo NamedRangeExistsError: value = Container(item) If Not InStr(1, CStr(value), "#REF!") > 0 Then NamedRangeExists = True End If Exit Function Exit Function NamedRangeExistsError: NamedRangeExists = False End Function 

Answer 4

Depending on the application you are making, it is useful to consider a dictionary. They are especially useful when you want to check if something exists. Take this example:

 Dim dictNames as Scripting.Dictionary Sub CheckRangeWithDictionary() Dim nm As Name 'Initially, check whether names dictionary has already been created If Not dictNames Is Nothing Then 'if so, dictNames is set to nothing Set dictNames = Nothing End If 'Set to new dictionary and set compare mode to text Set dictNames = New Scripting.Dictionary dictNames.CompareMode = TextCompare 'For each Named Range For Each nm In ThisWorkbook.Names 'Check if it refers to an existing cell (bad references point to "#REF!" errors) If Not (Strings.Right(nm.RefersTo, 5) = "#REF!") Then 'Only in that case, create a Dictionary entry 'The key will be the name of the range and the item will be the address, worksheet included dictNames(nm.Name) = nm.RefersTo End If Next 'You now have a dictionary of valid named ranges that can be checked End Sub 

As part of the main procedure, all you have to do is do an existence check before using the range

 Sub CopyRange_MyRange() CheckRangeWithDictionary If dictNames.exists("MyRange") then Sheets(1).Range("MyRange").Copy end if End Sub 

When loading the dictionary may look a little longer, it is very fast for processing and searching. It also becomes much easier to check if there is any named range referencing a valid address without using error handlers in this simple application.

Note that when using names at the sheet level, rather than at the workbook level, more complex keys must be used to ensure uniqueness. From how the dictionary was created, if the key is repeated, the value of the element is overwritten. This can be avoided by using the same Exists method as the check in the key creation statement. If you need a good link on how to use dictionaries, use one .

Good luck