Results 1 to 3 of 3

Thread: 2 Named Ranges - Same Name - Delete Only 1
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2013
    Location
    Manchester
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 2 Named Ranges - Same Name - Delete Only 1

    Hi All,

    I have inadvertently, through another piece of code, got a number of files that have 2 named ranges with the same name. I'd like to delete just one of these but not sure how i'd write the code to ensure I was deleting the correct one (the bottom of the 2 below):

    Name | Value | Refers To | Scope
    CashCompliance1 | 0% | = 'Cash Management'!$C$69 | Cash Management
    CashCompliance1 | {} | ='\\BMIFILES\Teams\Users\sgeoghegan\Desktop\Self Assessments WIP\[Self Assessment Hospital Template.xlsm]Cash Management'!$C$69| Workbook


    Apologies for the lack of image.

    I have all the loop code etc. to wrap around, and I've seen code for deleting ranges - but nothing specific to identify a range based upon on the "Refers To" section.

    Any help greatly appreciated.

    Thanks,
    Simon

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,627
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    5 Thread(s)

    Default Re: 2 Named Ranges - Same Name - Delete Only 1

    The following macro will loop through each name within the active workbook, and deletes the first name it comes across where "Refers to" starts with "='\\BMIFILES" . . .

    Code:
    Sub DeleteName()
    
        Dim nme As Name
        
        For Each nme In ActiveWorkbook.Names
            If Left(nme.RefersTo, 13) = "='\\BMIFILES\" Then
                nme.Delete
                Exit For
            End If
        Next nme
        
    End Sub
    Hope this helps!

  3. #3
    New Member
    Join Date
    Nov 2013
    Location
    Manchester
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 Named Ranges - Same Name - Delete Only 1

    Thanks Domenic, that worked brilliantly!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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