I have found that you can only have one named range referring to a particular
range. You can add more, but they are not visible or editable in the sheets.

So I am attempting to delete all the names on a worksheet prior to
rebuilding them. I used:

Dim theSheet As Worksheet
Set theSheet = Worksheets("Input")
For Each namedRange In theSheet.Names
theSheet.Names(namedRange.Name).Delete
Next namedRange

When I use this, the code is skipped over as theSheet.Names is empty.
However when I simply click around on the sheet I can see that there are
names there.

Does anyone know why this list appears empty even though there are ranges on
the sheet?

Maury

First, I've used multiple names that point at the same range and had no trouble
seeing them in the Insert|Name|define dialog.

Second, are you sure that the names are worksheet level names--not workbook
level names?

Third, since you're working with names, get Jan Karel Pieterse's (with Charles
Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp



Maury Markowitz wrote:
>
> I have found that you can only have one named range referring to a particular
> range. You can add more, but they are not visible or editable in the sheets.
>
> So I am attempting to delete all the names on a worksheet prior to
> rebuilding them. I used:
>
> Dim theSheet As Worksheet
> Set theSheet = Worksheets("Input")
> For Each namedRange In theSheet.Names
> theSheet.Names(namedRange.Name).Delete
> Next namedRange
>
> When I use this, the code is skipped over as theSheet.Names is empty.
> However when I simply click around on the sheet I can see that there are
> names there.
>
> Does anyone know why this list appears empty even though there are ranges on
> the sheet?
>
> Maury

--

Dave Peterson

try a simpler approach

for each n in sheets("Input").names
n.delete
next n

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> First, I've used multiple names that point at the same range and had no
> trouble
> seeing them in the Insert|Name|define dialog.
>
> Second, are you sure that the names are worksheet level names--not
> workbook
> level names?
>
> Third, since you're working with names, get Jan Karel Pieterse's (with
> Charles
> Williams and Matthew Henson) Name Manager:
>
> You can find it at:
> NameManager.Zip from http://www.oaltd.co.uk/mvp
>
>
>
> Maury Markowitz wrote:
>>
>> I have found that you can only have one named range referring to a
>> particular
>> range. You can add more, but they are not visible or editable in the
>> sheets.
>>
>> So I am attempting to delete all the names on a worksheet prior to
>> rebuilding them. I used:
>>
>> Dim theSheet As Worksheet
>> Set theSheet = Worksheets("Input")
>> For Each namedRange In theSheet.Names
>> theSheet.Names(namedRange.Name).Delete
>> Next namedRange
>>
>> When I use this, the code is skipped over as theSheet.Names is empty.
>> However when I simply click around on the sheet I can see that there are
>> names there.
>>
>> Does anyone know why this list appears empty even though there are ranges
>> on
>> the sheet?
>>
>> Maury
>
> --
>
> Dave Peterson