Iterate named range & clear contents

[Paul Wagstaff]

Excel 03

Hi all

I have a bunch of named ranges (Insert > Name > Define) which I wan to
iterate through, & then delete their contents. I'm trying this :

For Each nm in ThisWorkbook.Names
nm.ClearContents
Next nm

can someone please show me the correct way to do this.

thanks

paul

 

[Bob Phillips]

For Each nm In ThisWorkbook.Names
On Error Resume Next
Range(nm.Name).ClearContents
On Error GoTo 0
Next nm


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Rech]

On Error Resume Next
For Each NM In Names
Range(NM).ClearContents
Next
On Error Goto 0

 

[Paul Wagstaff]

thanks bob

ouch (*noise made after kicking myself*)

paul

 

[Dave Peterson]

Bob and Jim gave you code to do this, but save your workbook before you do it.

You may find that you're clearing more ranges than you thought. Excel can
create names that it uses for its own purposes.

For instance, try data|filter|autofilter, then run the code. Your filtered
range will be cleared.

You may want to be more specific with the names.

 

[Paul Wagstaff]

thanks for that. i have, as you say cleared out more stuff than i
anticipated. i've some 'print_ranges' that i'll need to exclude

thanks again
paul

 

[Dave Peterson]

One way to avoid the problem in the future is include some identifier in the
name--maybe a PW_ prefix???

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