Iterate named range & clear contents

  • Thread starter Thread starter Paul Wagstaff
  • Start date Start date
P

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
 
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)
 
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.
 
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
 
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
 
Back
Top