Deleting a named range in VBA

J

Jako

Could anybody please tell me how to delete a named range in VBA please.

I have a named range called "AUDITOR" and i have a routine that add
data to the range(which is stored for A2:A6 (for example).
But obviously when i've added more data to the list i want then t
assign the name "AUDITOR" to include the newly added item.

Any help appreciated.

TI
 
C

Chip Pearson

Jako,

You can delete a named range with code like the following.

ThisWorkbook.Names("TheName").Delete

However, if you just want to change the range to which the name
refers, you can use the Add method on the existing name, and this
will change the refers-to range. E.g.,

ThisWorkbook.Names.Add "TheName", Range("A1:A10")

In the above case, it doesn't matter that TheName already exists.
If so, it will be revised to refer to the new range.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
L

Leo Heuser

Hi Chip

I have recently discovered a strange phenomenon,
which looks like a bug to me.
Tested in Excel 97, 2000, 2002 and 2003 with
the same disturbing result.

Assuming a *global* name "TheName", which
e.g. is the range "A1:A10" on sheet1.

On sheet2 make a *local* name "TheName"
(e.g. cells B1:B5) with Insert > Name > Define
and in "Names in workbook" (or similar) enter
sheet2!TheName

Now, if sheet2 is **active**, when

ThisWorkbook.Names("TheName").Delete

is executed, the *local* name on sheet2 is deleted,
while the global one remains!!

If any other sheet is active during execution, the *global*
name is deleted, while the local one remains (of course!)

It really looks like, it's necessary to check for similar named
local names on the active sheet, when you want to delete a global one!
 
L

Leo Heuser

Hi Norman

Thanks for the pointer!
Nothing new under the sun, I guess.
(at least in this situation <g>)
 
Top