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