 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Iterate named range & clear contents
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  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Excel 03 > [quoted text clipped - 12 lines] > > paul thanks bob ouch (*noise made after kicking myself*) paul > For Each nm In ThisWorkbook.Names > On Error Resume Next [quoted text clipped - 18 lines] >> >> paul On Error Resume Next For Each NM In Names Range(NM).ClearContents Next On Error Goto 0 SignatureJim > Excel 03 > [quoted text clipped - 12 lines] > > paul 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. > Excel 03 > [quoted text clipped - 12 lines] > > paul SignatureDave Peterson 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 > Bob and Jim gave you code to do this, but save your workbook before you do > it. [quoted text clipped - 24 lines] >> >> 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 > 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 [quoted text clipped - 34 lines] > > > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.