A range has several names: how to find these names? vba

[lalbatros]

Hello,

I have a lot of ranges who have two names.

The first name is provided automatically by excel (query tables).
I cannot control this first name correctly.
The second name is defined by my application.

I would like to find a way to retrieve (in vba) the second name of such a given range.
Looping through all names definitions to find a match might be possible.
However, I fear this might not be reliable, and this might be also complicated.

Would you know of an easy and reliable way to find out all the names given to a range?

Thanks a lot,

Michel

 

[reitzen]

Here is a simple loop over the Names collection. I use this to delete named ranges that refer to external locations by looking for a square bracket in the RefersTo property. Similarly, you could search for all or part of the address.


Sub deleteInvalidNamedRanges()
'Deletes named ranges that refer to an external file
Dim n As Name
For Each n In Names
If InStr(1, n.RefersTo, "[") <> 0 Then
Debug.Print n.RefersTo & " - " & n.Name
n.Delete
End If
Next n
Set n = nothing
End Sub