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

--
Message posted from http://www.ExcelForum.com

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



"Jako " wrote in message
...
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
adds
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
to
assign the name "AUDITOR" to include the newly added item.

Any help appreciated.

TIA


---
Message posted from http://www.ExcelForum.com/

You can have it become self adjusting
=offset($a$2,0,0,counta(a:a),0)

--
Don Guillett
SalesAid Software

"Jako " wrote in message
...
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 adds
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 to
assign the name "AUDITOR" to include the newly added item.

Any help appreciated.

TIA


---
Message posted from http://www.ExcelForum.com/

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!

--
Best Regards
LeoH

Followup to newsgroup only please.

"Chip Pearson" skrev i en meddelelse
...
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

Hi Norman

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

--
Regards
LeoH


"Norman Jones" skrev i en meddelelse
...
Hi Leo,

See this earlier discussion:

http://tinyurl.com/2bguw


---
Regards,
Norman