Hi All

I've obviously missing something here and would appreciate any help.
I've seen a few similar threads but they haven't helped ....

I have an excel 97 workbook with a series of named ranges and would
like to delete them all. I'm suring the code ...

Sub a()


Worksheets(1).Select

For i = 1 To Worksheets.Count

Worksheets(i).Select

For n = 1 To ActiveSheet.Names.Count
strNAME = ActiveSheet.Names(n).Name
ActiveSheet.Names(n).Delete
Next n

n = 1
Worksheets(i).Select

Next i



End Sub

.... however when I run this code I keep getting error 9 from the
subscript being out of range.

I'm sure I'm making a simple mistake...

Thanks in advance

Cheers
Bevan

See:

http://groups.google.com/group/micro...c78b6698d1f38b
--
Gary's Student


"(E-Mail Removed)" wrote:

> Hi All
>
> I've obviously missing something here and would appreciate any help.
> I've seen a few similar threads but they haven't helped ....
>
> I have an excel 97 workbook with a series of named ranges and would
> like to delete them all. I'm suring the code ...
>
> Sub a()
>
>
> Worksheets(1).Select
>
> For i = 1 To Worksheets.Count
>
> Worksheets(i).Select
>
> For n = 1 To ActiveSheet.Names.Count
> strNAME = ActiveSheet.Names(n).Name
> ActiveSheet.Names(n).Delete
> Next n
>
> n = 1
> Worksheets(i).Select
>
> Next i
>
>
>
> End Sub
>
> .... however when I run this code I keep getting error 9 from the
> subscript being out of range.
>
> I'm sure I'm making a simple mistake...
>
> Thanks in advance
>
> Cheers
> Bevan
>
>

Names belongs to the workbook not the worksheet...
For n = 1 To ActiveWorkbook.Names.Count
ActiveWorkbook.Names(n).Delete
Next n
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


<(E-Mail Removed)>
wrote in message
Hi All
I've obviously missing something here and would appreciate any help.
I've seen a few similar threads but they haven't helped ....
I have an excel 97 workbook with a series of named ranges and would
like to delete them all. I'm suring the code ...

Sub a()
Worksheets(1).Select

For i = 1 To Worksheets.Count

Worksheets(i).Select

For n = 1 To ActiveSheet.Names.Count
strNAME = ActiveSheet.Names(n).Name
ActiveSheet.Names(n).Delete
Next n

n = 1
Worksheets(i).Select
Next i



End Sub

.... however when I run this code I keep getting error 9 from the
subscript being out of range.

I'm sure I'm making a simple mistake...

Thanks in advance

Cheers
Bevan

Be careful, there are some system ranges which you might want to avoid
deleting

The ones I know of are of the format

*_FilterDatabase
*Print_Area
*Print_Titles
*wvu.*
*wrn.*
*!Criteria


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jim Cone" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Names belongs to the workbook not the worksheet...
> For n = 1 To ActiveWorkbook.Names.Count
> ActiveWorkbook.Names(n).Delete
> Next n
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> <(E-Mail Removed)>
> wrote in message
> Hi All
> I've obviously missing something here and would appreciate any help.
> I've seen a few similar threads but they haven't helped ....
> I have an excel 97 workbook with a series of named ranges and would
> like to delete them all. I'm suring the code ...
>
> Sub a()
> Worksheets(1).Select
>
> For i = 1 To Worksheets.Count
>
> Worksheets(i).Select
>
> For n = 1 To ActiveSheet.Names.Count
> strNAME = ActiveSheet.Names(n).Name
> ActiveSheet.Names(n).Delete
> Next n
>
> n = 1
> Worksheets(i).Select
> Next i
>
>
>
> End Sub
>
> ... however when I run this code I keep getting error 9 from the
> subscript being out of range.
>
> I'm sure I'm making a simple mistake...
>
> Thanks in advance
>
> Cheers
> Bevan
>