On Jul 2, 12:58*pm, "Peter T" <peter_t@discussions wrote:
I think I see what's happening. You add a new Query named "WIP" to your
sheet, which in turn automatically adds a similarly named range.

You delete both the Query and the named range.

You add the same named Query back again, it gets re-named as WIP_1 and also
defines a similarly named range.

The problem appears more related to the Query name rather than the named
range, which merely gets named to same as the Query. After deleting the
Query it's name is not fully removed, when you add the same named query it
gets renamed with the underscore and extension, and the Named range
similarly.

It seems the only way to reset is to save the workbook (after deleting the
Query tables & names), close it and reopen.

Alternatively just live with it in the knowledge that the Named range will
be the same as that of your Query of the same name.

FWIW object counters of shapes are not decremented when they are deleted,
all must be deleted and do the save/close/reopen to reset.

Regards,
Peter T

"KieranH" wrote in message

...
To clarify:

My code does the following:

* * 1. Clears all named regions and query tables on a partiular
worksheet.

* * 2. Connects to a remote SQL server and pull back data into a
series of query tables using a series of sp calls.

* * 3. Legally names the region using the name property of the query
table *i.e *' WIP'

However, when I check the worksheet manually *the named region is not
'WIP' *as expected but 'WIP_1'
I need to re-use the same name as charts and tables reference the
data. *It is as if *Excel will not use my chosen name as it it
believes it to be already in use - thus adding an unwanted underscore
and incremental index.

I appreciate that I could parse and correct the names but I felt I
must be missing something!

Hope this is clearer

Regards

Kieran

On Jul 1, 5:53 pm, "Jim Rech" wrote:



That suggests that you're trying to create an illegal name. You cannot for
instance create a name with a space or that looks like a cell address.

--
Jim"KieranH" wrote in message

...
| Hi,
|
| I realise that this has probably been discussed previously but I
| failed to find a useful thread.
|
| I'm creating named regions programatically (corresponding to
| querytables)
| but I end up with an underscore and a number following my chosen name.
|
| What is the best way of correcting / preventing this behaviour
|
| Note that my code attempts to clear existing names with the
| following:
|
| With ThisWorkbook.Sheets("data")
| .Cells.Clear
| For Each qt In .QueryTables
| qt.Delete
| Next
| For Each nName In .Names
| nName.Delete
| Next
| End With
|
| Any help would be appreciated
|
| Regards
|
|Kieran- Hide quoted text -

- Show quoted text -

Thanks for your response,
I circumvented the problem in the end by deleting the entire sheet and
re-creating it programatically. This allowed me to maintain the
desired region names.

It was important because the "users" reference these region names in
their worksheets

Cheers

Kieran