Cant get named ranges

[vba317]

I am working with excel 2010. I am trying to write a script to go through the named ranges of the workbook and delete ones with #REF errors and add ones that don't exist. Currently I am using the Names range but I only get the last part of the range not the whole range so my code does not work. This is my first post to the board.

For Each nName In Names
'
'    strRangeName = "Dwelling_Territory"
'


'    If nName = strRangeName Then
'        ActiveWorkbook.Names(strRangeName).Delete
'    Else
'    'Add Named Range
'        ActiveWorkbook.Names.Add Name:=strRangeName, RefersToR1C1:= _
'        "=Tables!R4C703"
'    End If
    'If Named Range does not exist
'     If RangeNameExists(strRangeName) = False Then
'    If nName <> strRangeName Then
'      ActiveWorkbook.Names.Add Name:=strRangeName, RefersToR1C1:= _
'        "=Tables!R4C703"
'     End If

 

[Fishboy]

Hi vba317, welcome to the boards.

Depending on how many named ranges you already have, and whether they are always fixed the same or expand, the following snippet of code may be of use to you. Remeber to test it out on a COPY of your workbook first however:

Sub TEST()
' Deletes the named range "TEST1" from the active workbook
    ActiveWorkbook.Names("TEST1").Delete
' Creates a new named ranged based on the criteria below
        Range("'Sheet1'!$A$1:$A$10").Name = "TEST1 New"
' Displays a message box to confirm the process completed
    MsgBox "Named Ranges Recreated"
End Sub

In the above code you can change the bold red text to suit your named range to be deleted.

You can change the bold blue text to suit the range covered by the named range and then change the bold orange text to whatever you want the replacement named range called (it can be exactly the same as the one it replaces if you prefer, I just called it new so you can see it work if you test it out).

You are not limited to doing this one at a time or that would be pointless. See the example below to see how this works with multiple ranges instead:

Sub TEST2()
    ActiveWorkbook.Names("TEST1").Delete
    ActiveWorkbook.Names("TEST2").Delete
    ActiveWorkbook.Names("TEST3").Delete
    ActiveWorkbook.Names("TEST4").Delete
        Range("'Sheet1'!$A$1:$A$10").Name = "TEST1 New"
        Range("'Sheet1'!$B$1:$B$10").Name = "TEST2 New"
        Range("'Sheet1'!$C$1:$C$10").Name = "TEST3 New"
        Range("'Sheet1'!$D$1:$D$10").Name = "TEST4 New"
    MsgBox "Named Ranges Recreated"
End Sub

As mentioned above this is only worthwhile if you don't have hundreds of named ranges as you would have to type out 2 lines of code for each one (one to delete, the other to recreate), but if your collection of named ranges is more manageable this may well be a possible solution for you.

I hope this helps.

 

[vba317]

Thank you for your response. I originally tried this method, I have over 50 ranges so I was hoping to avoid it. The problem I had was the delete code would give me an error if the named range was already deleted. What can I add to get around that?

 

[Fishboy]

Out of interest, are there other functions that could have deleted the range already or will it have been done manually? It strikes me that really the macro should be the only thing removing and recreating the named ranges so in theory you should not encounter this issue.

That said, there are ways of checking "If range exists then..." which could be used to determine if something even needs deleting or not before attempting a delete. I will need to look it up however as it is something I personally have not used myself in the past.

 

[StephenCrump]

You could delete your range names with #REF! errors using:

If InStr(nName.Value, "#REF!") Then nName.Delete

If you have a list of range names that you want to make sure have been set correctly, couldn't you just use code like this:

Sheets("Sheet1").Range("A1").Name = "SomeRangeName1"
Sheets("Sheet2").Range("A1:B2").Name = "SomeRangeName2"
..... etc

i.e. without bothering to check whether the range names already existed?

 

[Fishboy]

Wouldn't that give an error if the range already exists?

 

[vba317]

Yes I added code to check if the range already exists. I have been assigned another project. So this qustion can be closed.