Delete all named ranges in a workbook
 

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

Delete all named ranges in a workbook
 

See:

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


" 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

Delete all named ranges in a workbook
 

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



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

Delete all named ranges in a workbook
 

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" wrote in message
...
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



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