check for dupicate Range Name on Current sheet

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
Hi,

Can any one point me in the right direction?

What I want to do is when I name a new range I want to check there isnt a range on the current sheet already with that name.

cheers

Ps Will this work?

Code:
Function NameExists(TheName As String) As Boolean
On Error Resume Next
NameExists = Len(ThisWorkbook.Names(TheName).Name) <> 0
End Function
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
right that works great but I now have the problem if i delete the range it still thinks it exists.

is there a beter way of doing this? Or What am i doing wrong?

J
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi jjyxk845

I've looked at your post and there are somethings I don't understand completely. Can you please explain?

First you say

"when I name a new range I want to check there isnt a range on the current sheet already with that name"

I believe that what you mean is you want to check is a name already exists, it doesn' matter if it points to a range or not.

Second you say:

if i delete the range it still thinks it exists

Have you deleted the name or just the range it is pointing to?

I tried your code and it seems to be working OK.

Kind regards
PGC
 

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
right heres a copyof my code.
I think your right I'm getting confused over ranges and names.

What I'm tring to do is summarize a range and give it the name of the to first cell. this all works fine but if i highlight the cells which make up the range and delete them so name nolonger appears in the top left drop down list. and try and recreate the range it tells me it still exists.

Also is there any way I can i can detect if It alread exists its not just overwriting the orginal named range. Ie i run the macro on the same set of cells again?

I hope I'm makeing more sense now??? :oops:

Code:
Function name_summa_CC()
Dim Col As Integer
Dim Col2 As Integer
Dim Row As Integer
Dim TotalRange As Range
Dim Rangeselected As Range
Dim RangeName As String
Dim intI As Integer
Dim check_name As Integer
Dim CheckThisOne As String

If ActiveCell.CurrentRegion.Row < 7 Then
MsgBox "     Not enough free rows above data to add summaries" & Chr(13) & Chr(13) & "Names need to be on at least row 7 and preferably row 13", vbOKOnly + vbCritical, "Naming Error"
End If

    Col = ActiveCell.Column
    Set TotalRange = ActiveCell.CurrentRegion
    Col2 = TotalRange.Column
    Row = TotalRange.Row
    
        
    TotalRange.Columns(Col + 1 - Col2).Select

    Set Rangeselected = Selection
    
    RangeName = Rangeselected.Cells(1, 1).Value
    CheckThisOne = Rangeselected.Cells(1, 1).Value
    If IsValidName(CheckThisOne) = False Then
    Exit Function
    End If
    
    
    
    
    Selection.CreateNames Top:=True
    
    RangeName = Rangeselected.Cells(1, 1).Value
    
        ActiveSheet.Cells(Row, Col).NumberFormat = "#,##0"
        ActiveSheet.Cells(Row - 2, Col).Formula = "=counta(" & RangeName & ")"
        ActiveSheet.Cells(Row - 2, Col).Font.Bold = True
        ActiveSheet.Cells(Row - 3, Col).Formula = "=sum(" & RangeName & ")"
        ActiveSheet.Cells(Row - 4, Col).Formula = "=average(" & RangeName & ")"
        ActiveSheet.Cells(Row - 5, Col).Formula = "=min(" & RangeName & ")"
        ActiveSheet.Cells(Row - 6, Col).Formula = "=max(" & RangeName & ")"
        ActiveSheet.Cells(Row, Col).EntireColumn.AutoFit
       
End Function


Code:
Function IsValidName(nm As String) As Boolean
  '+++++++++++++++++++++++++++++++++++
  'Check for Duplicate Range Names
  '+++++++++++++++++++++++++++++++++++
   On Error Resume Next
   IsValidName = Len(ThisSheet.Names(nm).Name) <> 0
   If IsValidName = False Then
   MsgBox CheckThisOne & " Already Exists"
   Exit Function
   End If
  '+++++++++++++++++++++++++++++++++++
    'Check Is Not cell reference
  '+++++++++++++++++++++++++++++++++++
    On Error Resume Next
    With ActiveWorkbook.Names
    .Add Name:=nm, RefersTo:=Range("A1")
    .Item(nm).Delete
    End With
    IsValidName = IIf(Err, False, True)
    On Error GoTo 0
    If IsValidName = False Then
    MsgBox CheckThisOne & " Must Not be a cell reference"
    Exit Function
    End If
  '++++++++++++++++++++++++++++++++++
  'Check Length > 2
  '+++++++++++++++++++++++++++++++++++
    If Len(nm) <= 2 Then
    IsValidName = False
    MsgBox CheckThisOne & " Name must be greater than 2 Characters"
    Exit Function
    End If

End Function
Code:
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi again

If you delete the cells but do not delete the name, the name still exists, although pointing to an invalide reference.
You can use the name again by changing the RefersTo property. If you prefer, just delete it and it will be available again.

If the name already exists and you want to check if it already refers to a range you can check the addresses of the range and of the name

for example

If range("A1:A4").name.name=RangeName

If range ("A1:A4") is not a named range you'll get an error.

Anyway don't forget that a name may refer to a range but may also refer to a formula or to a constant. So the fact that a name exists doesn't mean there is a range associated to it.

Hope this helps
PGC
 

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
OK cheers thanks for that.

I've now changed the code to this. And it works great just got to add a bit to check if its the same range.

Cheers for the help!
:biggrin:
J

Code:
 On Error Resume Next
   Dim nmlength As Integer
   Dim nmname As String
   nmlength = Len(ThisWorkbook.Names(nm).Name)
   nmname = ThisWorkbook.Names(nm)
   If nmlength <> 0 Then
   IsValidName = False
    If InStr(nmname, "#REF!") Then
       IsValidName = True
    Else
       MsgBox CheckThisOne & " Already Exists"
       Exit Function
    End If
   End If
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

I'm glad I was able to help.

Cheers
PGC
 

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
Ok now i'm confused on this checking if the the name alread refers to the selected range! :oops:

my current selection contains the cells and the cell with the name in it

ie

a1:a5 with the future range name in a1

I am now checking to see if the name already exists but if it does how do i see if it refers to the same selection ie a2:a5 (because a1 is just the name)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
You can use the Name property of the Range object. If it's a named range you'll get the corresponding Name object.

Like:

Code:
If Range("A2:A5").Name.Name = Range("A1") Then
    MsgBox ("Match!")
End If

or, if you have the range in a variable, you have to manipulate it to exclude the first cell.
Assuming like in your example that it's a vertical vector range

Code:
Dim rR As Range

Set rR = Range("A1:A5")

...

If Range(rR.Cells(2), rR.Cells(rR.Rows.Count)).Name.Name = rR.Cells(1) Then
    MsgBox ("Match!")
End If

Or, also

If rR.Resize(rR.Rows.Count - 1).Offset(1).Name.Name = rR.Cells(1) Then
    MsgBox ("Match!")
End If

I hope this helps
PGC
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
P. S. If there is no name associated with the range you'll get an error that you must trap with the On Error. You may have to rearrange the code a little bit but I see that it will not be a problem for you.
 

Forum statistics

Threads
1,137,193
Messages
5,680,086
Members
419,880
Latest member
suarezprado

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top