Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Test If Named Range Exists

  1. #1
    Join Date
    7th September 2005
    Posts
    72

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2007
    Posts
    759

    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 at 09:46.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    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;
    VB:
    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 
    
    

  4. #4
    Join Date
    7th September 2005
    Posts
    72

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

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by cscribner; September 8th, 2007 at 03:00. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    14th July 2007
    Posts
    759

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Test If Named Range Exists

    Or this ...
    VB:
    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 at 06:45.
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  7. #7
    Join Date
    7th September 2005
    Posts
    72

    Re: Test If Named Range Exists

    Thanks for your help! Works great.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Check If Named Range Exists Before Delete
    By Averilp in forum EXCEL HELP
    Replies: 2
    Last Post: November 29th, 2007, 15:27
  2. Test if Sheet Exists
    By minitman in forum EXCEL HELP
    Replies: 5
    Last Post: May 15th, 2007, 18:12
  3. Test/Check if Shape Exists on Worksheet
    By Stef32 in forum EXCEL HELP
    Replies: 6
    Last Post: August 31st, 2006, 18:48
  4. If syntax to test if command button exists
    By mikeburg in forum EXCEL HELP
    Replies: 1
    Last Post: September 30th, 2005, 00:55
  5. Check if Named Range exists
    By siroche in forum EXCEL HELP
    Replies: 8
    Last Post: January 18th, 2005, 09:34

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno