Announcement

Collapse
No announcement yet.

Test If Named Range Exists

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Test If Named Range Exists



    Is there a way to check if a named range exists before I run a piece of code? I created a new file that has need for all the old file's ranges plus a couple more, and I want to use the same macro for both. So on the first file I just want to say, if these other named ranges are there, go ahead and do his other thing.

  • #2
    Re: Check If Named Range Exists

    There is another thread with the exact same title as yours.
    http://www.ozgrid.com/forum/showthread.php?t=28474

    Did it not answer your question?
    Last edited by Dave Hawley; September 7th, 2007, 09:46.

    Comment


    • #3
      Re: Check If Named Range Exists

      As mentioned above, there is at least 1 thread with the same question and has been answered. Here is the answer;
      Code:
          Dim rRangeCheck As Range
          
          On Error Resume Next
          Set rRangeCheck = Range("MyRange")
          On Error GoTo 0
          If rRangeCheck Is Nothing Then
             'No name
          Else
             'Go for it
          End If

      Comment


      • #4
        Re: Test If Named Range Exists

        I'm sorry about that. I really did run a search for "Named Range Exists", but now that I look more carefully at the search results, the message you found showed up on the third page (number 53 out of 61 responses). I'm not used to something so clearly relevant being so far back in the results, but I promise to be more thorough next time.

        Thanks for your help--I really appreciate it!
        Auto Merged Post;

        When I try the code you've given me, it only seems to work if the missing named ranges are looked for before the ones that exist. My macro looks like this:
        Code:
        Sub CheckRanges()
            Dim rRangeCheck As Range
            Dim Sections(4) As Variant
                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
                Set rRangeCheck = Range(Sections(i))
                On Error GoTo 0
                If rRangeCheck Is Nothing Then
                     MsgBox ("This Range: " & Sections(i) & " does NOT exist!")
                Else
                     MsgBox ("This Range: " & Sections(i) & " DOES exist!" _
                                & vbCrLf & vbCrLf & _
                                "its address is: " & rRangeCheck.Address)
                End If
            Next i
        End Sub
        And even though Sections(3) and (4) don't exist, the checker tells me they do, and that their address is the same as the last one that was found: Sections(2), ie. Range("GHI"). But if I change the order and put the non-existent ranges first, the error code works like a charm:

        Code:
            Dim Sections(4) As Variant
                Sections(4) = "ABC" 'This one exists
                Sections(3) = "DEF" 'This one exists
                Sections(2) = "GHI" 'This one exists
                Sections(1) = "JKL" 'This one DOES NOT exist
                Sections(0) = "MNO" 'This one DOES NOT exist
        I'm uploading the excel document for your reference. Does anybody know why this would be?
        Attached Files
        Last edited by cscribner; September 8th, 2007, 03:00. Reason: Auto Merged Doublepost

        Comment


        • #5
          Re: Test If Named Range Exists

          the message you found showed up on the third page (number 53 out of 61 responses)
          I wasn't talking about a search in Google or Yahoo or whatever search engine you used; I am talking about a search of this forum. When you posted you question, you should have seen 5 possible answers. The link I provided was number 3 of 5.

          Does your variable "Sections" need to be a variant? Since it seems to be holding a range name, i.e. a string, I would change it to a string variable. Assuming that will work for you, I have modified your code as follows:
          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

          Comment


          • #6
            Re: Test If Named Range Exists

            Or this ...
            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
            Last edited by shg; September 8th, 2007, 06:45.
            Entia non sunt multiplicanda sine necessitate.

            Comment


            • #7


              Re: Test If Named Range Exists

              Thanks for your help! Works great.

              Comment

              Working...
              X