More than 1 name for a range

[bertiebj]

I use named ranges a lot both to identify particular ranges on a sheet, and as markers to identify certain properties of a sheet so that when an auto command happens, it can tailor what it does for the sheet.

I have got to the stage whereby I cannot rely on a range - or individual cell - not having more than 1 name. Certainly for Excel 2010, it looks like the range object does not have the collection property 'Names'. It has the property Name, but not Names. If there is more than 1 name for a cell/range, then it appears to assign the first alphabetic name to the range 'name' property.

This is causing me an issue as ideally I need to test a cell when selected to quickly see whether the name of the cell/range matches a certain format. If there is more than 1 name for the cell I clearly need to see if either/any of the names match this format. This does not appear to be simple as I have no way of knowing if there is more than 1 name for the range.

I have other solutions such as checking all the names on the worksheet to see which ones match with the current cell/range, but there are many names and this does add delay into the process. The quickest I can do it is to see if the cell has a name, if so and it doesn't match the format, then go do a full scan of names for the worksheet to see if any are for the cell and match the name format.

Does anyone have any ideas? Such as whether you can [quickly] see if there are more than 1 names for the cell/range; or perhaps a novel way of more quickly testing the names in the sheet?

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

I don't think there is a way other than looping around the Names collection, sorry.

 

[bertiebj]

Welcome to MrExcel.

I don't think there is a way other than looping around the Names collection, sorry.

That is what I thought.

I have thought, but couldn't get it to work, that if I came across a name for cell, that I may be able to delete that name, refresh my range object for the cell, see if there is still a name present - which should be a new name, and see if that name matches, and then restore the name. If I could get this to work, presumably I would have to be prepared to store several names!

Any idea why this would not work? If it did my concerns are that deleting and restoring names is a little dangerous.

 

[mikerickson]

This should reduce things to one name per range.
The commented out lines are just to let you know what names were removed (colB) or kept (colA)

Dim oneName As Name
Dim testName As Name

For Each oneName In ThisWorkbook.Names
    For Each testName In ThisWorkbook.Names
        If testName.RefersToRange.Address(, , , True) = oneName.RefersToRange.Address(, , , True) Then
            If (testName.Name <> oneName.Name) Then
                'Range("B65536").End(xlUp).Offset(1, 0).Value = testName.Name & "; " & testName.RefersTo
                testName.Delete
            Else
                'Range("A65536").End(xlUp).Offset(1, 0).Value = testName.Name & "; " & testName.RefersTo
            End If
        End If
    Next testName
Next oneName

 

[Andrew Poulsom]

This worked for me:

Sub Test()
    Dim N As String
    Dim i As Long
    Dim ArrNames() As Variant
    On Error Resume Next
    With Range("A1")
        Do
            N = .Name.Name
            If Err = 0 Then
                i = i + 1
                ReDim Preserve ArrNames(1 To i)
                ArrNames(i) = N
                ActiveWorkbook.Names(N).Delete
            Else
                Exit Do
            End If
        Loop
        On Error GoTo 0
        For i = LBound(ArrNames) To UBound(ArrNames)
            .Name = ArrNames(i)
        Next i
    End With
End Sub

 

[bertiebj]

Thanks Andrew.

No sure what I was doing wrong - sometimes the main thing in writing code is the belief that it must work - once you know that you make it work!

Now that I know it works I will this idea of deleting names for the range to expose other names, and then set back up the deleted names once I have either found or not found the name format that I am looking for.

Many thanks

 

[bertiebj]

Hi Mike

Thanks for your assistance but I may not have explained myself properly. I was not trying to delete names where there were 2 or more for a range, but simply expose multiple names where there were more than 1 for a cell. This was so that I may test the actual names to see if they accorded with a format that indicated I had to do a certain task. In addition this had to be fairly efficient so that the moving about the screen was not too sticato.

Many thanks