2 Named Ranges - Same Name - Delete Only 1

[SimonGeoghegan]

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

 

[Domenic]

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

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!

 

[SimonGeoghegan]

Thanks Domenic, that worked brilliantly!