Removing & Adding Range Names in code - quick method

Question

With my worksheet refresh process I start by deleting columns that contain a named range "dogs"

(The name referstoRange then contains a #ref error)

Subsequently an updated range "dogs is named with: Range("A1:A17").Name = "dogs"

The "correct" methods are listed below, but for my question, are My "quick methods acceptable?

ActiveWorkbook.Names("dogs").Delete

ActiveWorkbook.Names.Add Name:="dogs", RefersToR1C1:="=Sheet1!R1C1:R17C1"

Answer 1

The line

Range("A1:A17").Name = "dogs"

will assign the name "dogs" to A1:A17 regardless of whether "dogs" was already a defined name or not. There is no need to delete the name OR the range that the name referred to before reassigning the name.

---

Regards, Hans Vogelaar

Answer 2

Thanks Hans,

What I'm saying is that I need to delete the columns that contain the name for reasons other than deleting the name.

This leaves the #ref error with the "old" name at that point

Then:

Range("A1:A17").Name = "dogs"

for adding a "New" name

All this seems OK to me but at the same time looks a bit grubby with the #ref error

How would you do the 2 steps here?

Answer 3

It doesn't really matter that "dogs" temporarily has a #REF! error, unless it causes your macro to stop with an error message.

---

Regards, Hans Vogelaar

Answer 4

I don't follow what you're really trying to do but if you've got Ref errors in Names, typically due to deleting the entire rows or columns of the reference, it's best to identify them first, eg

Sub NameErrors()
Dim nm As Name
     For Each nm In ActiveWorkbook.Names
             If InStr(nm.RefersTo, "#REF!") > 0 Then
                     i = i + 1
                     Cells(i, 1) = nm.Name
                     Cells(i, 2) = "'" & nm.RefersTo
             End If
     Next
End Sub

With your specific knowledge of what the names once referred to maybe you can correct "in cells" and with a simple macro recreate corrected names.

You might also want to search the workbook for where the names are used, it's easy enough to search cell formulas but names can be used in various other places, eg Series formulas. That might not be an issue for you but if you don't have it already the "Name Manager" addin (Jan Karel Pieterse & Charles Williams) is very helpful

http://www.jkp-ads.com/officemarketplacenm-en.asp

Peter Thornton