How to test if named range exists
Category: [Excel] Demo Available
How to test if named range exists
Requirement:
Is there a way to check if a named range exists before the user runs a piece of code? The user created a new file that has need for all the old file's ranges plus a couple more, and wants to use the same macro for both. So on the first file the user just want to say, if these other named ranges are there, go ahead and do his other thing.
Solution:
Code:
Sub CheckRanges()
Dim rRangeCheck As Range
Dim Sections(4) As String
Dim x As Long
Sections(0) = "ABC" 'This one exists
Sections(1) = "DEF" 'This one exists
Sections(2) = "GHI" 'This one exists
Sections(3) = "JKL" 'This one DOES NOT exist
Sections(4) = "MNO" 'This one DOES NOT exist
For i = 0 To 4
On Error Resume Next
x = Len(ThisWorkbook.Names(Sections(i)).Name)
On Error GoTo 0
If x <> 0 Then
Set rRangeCheck = Range(Sections(i))
MsgBox ("This Range: " & Sections(i) & " DOES exist!" _
& vbCrLf & vbCrLf & _
"its address is: " & rRangeCheck.Address)
x = 0
Else
MsgBox ("This Range: " & Sections(i) & " does NOT exist!")
End If
Next i
End Sub
or
Code:
Sub CheckRanges()
Dim vNames As Variant, v As Variant
vNames = Array("ABC", "DEF", "GHI", "JKL", "MNO")
For Each v In vNames
Debug.Print v, RangeExists(CStr(v))
Next
End Sub
Function RangeExists(s As String) As Boolean
On Error GoTo Nope
RangeExists = Range(s).Count > 0
Nope:
End Function
Obtained from the OzGrid Help Forum.
Solution provided by royUK.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA script to count files/subfolders using a range from WB for the root folder |
| How to add sequential numbers between values, within a range |
| How to locate numbers 1 to 10 in a range |
| How to loop through different ranges |
| How to select the first coloured cell in a range |
| How to use an input box to enable a range of cells to autofill |
| How to use loop IF, if range is unknown |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.