VBA Check if Range Name exists 3

[PeterMoran]

Hi,

I have this code which creates a Range Name:

    ActiveWorkbook.Names.Add Name:=Worksheets("Ladders").Range("B2").Value, RefersToR1C1:= _
        "=Ladders!R22C2:R39C17"

I would like to include a prior check if this name exists - the value in cell B2, and if so delete the Range Name prior to to the above code.

Any code would be appreciated!

Thanks in advance.

Peter Moran

 

[xlbo]

For Each rngnm In ThisWorkbook.Names

If UCase(rngnm.Name) = UCase(Worksheets("Ladders").Range("B2").Value) Then

    rngnm.Delete

End If

Next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

What about this ?

Dim sName As String, oName As Name
sName = Worksheets("Ladders").Range("B2").Value
For Each oName In ActiveWorkbook.Names
  If oName.Name = sName Then
    oName.Delete
    Exit For
  End If
Next
ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:="=Ladders!R22C2:R39C17"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

If the name already exists in the workbook, your code will overwrite it, in practice it will change its reference.

combo

 

[PeterMoran]

Hi Guys,

Thanks for the responses.

I was reluctant to assume that the properties of the range name would be overridden, although that is what happens when using Excel manually.

I presume your use of "UCase" Geoff, just ensures that case differences in names is avoided.

Regards,

Peter Moran

 

[xlbo]

Hi Peter - yup - that's what it's there for

If you're gonna use the loop definitely add in PHVs "Exit For" so it doesn't keep on looping after the name is deleted

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DaveInIowa]

Here's another option to go right for the name without any looping:

    Dim nameThing As Name
    Dim nameValue As String
    
    nameValue = Worksheets("Ladders").Range("B2").Value
    
    On Error Resume Next
    Set nameThing = ActiveWorkbook.Names(nameValue)
    On Error GoTo 0
    
    If (nameThing Is Nothing) Then
        MsgBox "Name '" & nameValue & "' is not defined."
    Else
        MsgBox "Name '" & nameValue & "' is defined."
    End If

 

[SkipVought]

Hopefully, the Name refers to a range definition that yesterday means the same thing as today (not necessarily the same range, but the same logical thing).

As such, you need not delete anything, merely define/redefine it.

I most often use table headings as the souce for my range names. I usually execute or refresh a query on my sheet and incorporate similar code...

    With wsList
        With .QueryTables(1)
            .Connection = sConn
            .CommandText = sSQL
            .Refresh False
'this adds/replaces table column names
            Application.DisplayAlerts = False
            .ResultRange.CreateNames True, False, False, False
            Application.DisplayAlerts = True
'this adds/replaces table name as the contents of sName
            ActiveWorkbook.Names.Add _
                Name:=sName, _
                RefersTo:="='" & .Parent.Name & "'!" & .ResultRange.Address
        End With
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}