Iterate 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 


0
8/11/2007 10:41:37 AM
excel 39879 articles. 2 followers. Follow

6 Replies
822 Views

Similar Articles

[PageSpeed] 15

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)

"Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in message 
news:Bngvi.48041$7c.8158@fe2.news.blueyonder.co.uk...
> 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
> 


0
bob.NGs1 (1661)
8/11/2007 11:56:38 AM
    On Error Resume Next
    For Each NM In Names
        Range(NM).ClearContents
    Next
    On Error Goto 0


-- 
Jim
"Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in message 
news:Bngvi.48041$7c.8158@fe2.news.blueyonder.co.uk...
> 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
> 


0
jrrech (1932)
8/11/2007 12:17:44 PM
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 wrote:
> 
> 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

-- 

Dave Peterson
0
petersod (12005)
8/11/2007 12:36:48 PM
thanks bob

ouch (*noise made after kicking myself*)

paul



"Bob Phillips" <bob.ngs@somewhere.com> wrote in message 
news:efWYo6A3HHA.1484@TK2MSFTNGP06.phx.gbl...
> 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)
>
> "Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in message 
> news:Bngvi.48041$7c.8158@fe2.news.blueyonder.co.uk...
>> 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
>>
>
> 


0
8/11/2007 12:36:48 PM
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" <petersod@verizonXSPAM.net> wrote in message 
news:46BDAD60.8189BF2C@verizonXSPAM.net...
> 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 wrote:
>>
>> 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
>
> -- 
>
> Dave Peterson 


0
8/11/2007 1:08:40 PM
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

Paul Wagstaff wrote:
> 
> 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" <petersod@verizonXSPAM.net> wrote in message
> news:46BDAD60.8189BF2C@verizonXSPAM.net...
> > 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 wrote:
> >>
> >> 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
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
8/11/2007 2:27:51 PM
Reply:

Similar Artilces:

Splitting content of cell into 2 cells
Hello, I've got Excel 2002. I have a spreadsheet with 20,000+ rows. Column B of each row contains a date and time stamp in this format: mm/dd/yyyy hh:mm (Note that if any field is a single digit there is not a 0 place holder - i.e. March 1st would appear as 3/1/2006 and not 03/01/2006. Same thing for the hours.) Is there a way to automatically pull the hh:mm part out of column B and have it put into Column C (which is empty)? Thanks. Mark Highlight column B. Select Data menu, Text to Columns command. Wizard should take you the rest of the way through (choose delimited on 1s...

Printing all worksheet names
Does anyone know if it possible to print a list of worksheet names? A list similar to the one you would find in the "Contents" tab of the files' Properties. Thanks for any help you can provide. Programme - XP Excel Hi This topic was covered lately in microsoft.excel.worksheet.functions NG (thread 'print sheet tab names' started by JDB at 06.01.2004 16:00). Here is the solution from me: Create an UDF --- Public Function TabByIndex(TabIndex As Integer) As String Application.Volatile TabByIndex = Sheets(TabIndex).Name End Function --- On some empty worksheet,...

Big problem! DNS name Space.
I have a need to share one DNS name space between four AD Exchange forest ( I know what your thinking, why would you do this! All I can say it’s government). All the forests are running Windows 2000 with Exchange 2003. My problem is I can’t seem to find a way to allow this to happen. I know it’s easy to accomplish this if it was just two forests, but how do you share the same E-mail address across four AD forest? Basically I need mail to come in into the first Exch Org and if the recipient is not found on that Exch Org to continue down the line to the next Org, in till the user is foun...

Command button
[Excel 2003] I have command buttons on the userform which access other userforms (which drive the spreadsheet data entry) I have a listbox on the userform displaying a number. I would like to enter code into the command button activations such that if the number in the listbox does not equal the number stored within the code of the command button then a mesage box is displayed and the command button does not activate its sub routines. Can anyone help? Thanks, Roger Something like this: Option Explicit Public Property Get MayProceed() As Boolean Const M...

Windows 2000 server user names
Where can I configure the user names formatting like you do in Recipient policies? yaro ...

sender domain name?
I have one Exchange 2003 in a semi-lab setup. It is part of my lab domain, we'll call it test.domain, so my server is mail.test.domain Obviously test.domain is not resolveable from the Internet and when I send mail, it goes out as test.domain. How can I make this server send mail out as mail.blah.com so its resolveable? (and will quit bouncing all my messages to domains that do reverse dns lookups?) Thanks for any and all help!! -Mike Change the addressing. You might consider adding a subdomain to your production domain like test.company.com, registering it with an MX record ...

Account Names not showing when adding new user
Hi, I created a SharePoint site off of a 2007 Project Server install and when adding users to access the SharePoint site, directory services only returns admin users of the SharePoint/Project Server? How do I get AD to return all the users in the domain? Thanks! Steve Steve: What do you mean when you say "I created a SharePoint site off of a 2007 Project Server install?" Your description lacks a few details. "Steve" <Steve@discussions.microsoft.com> wrote in message news:EAA4C48D-9D6A-44C7-84BF-787AE762561E@microsoft.com... > Hi, >...

Counting how many different names in a long list
I have a list of 332 names many of them are duplicated. I am looking for a formula or function that can count how many unique names there are in the list. Can anyone help? -- Quaisne ------------------------------------------------------------------------ Quaisne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28052 View this thread: http://www.excelforum.com/showthread.php?threadid=501357 This'll count the number of distinct values in a range: =SUMPRODUCT((A1:A99<>"")/COUNTIF(A1:A99,A1:A99&"")) (adjust the range to matc...

Serializing Array with array "name" in the XML
I need help serializing an array without including the array "name". I am writing in C# and using the XmlSerializer to serial classes. I am trying to serialize a class with an array in it like the one below. public class myclass { public imagecontext; public myimage[] images; } public myimage { public string name; public string path; } It will serialize something like this... <myclass> <imagecontext>my context</imagecontext> <images> <myimage> <name>Image1</name> ...

State table lookup
Hi I have a form for entering Customer data. When entering a new record, I have a lookup combo box to validate the state that the user enters. The combo box displays the state code and the state name. (ie. Ca and California). The state code is the bound value and the state name is the display value. The only time I want to display the full state name is when the user is entering data into the form. I want the state code to be displayed on queries, forms, and reports. Is there a way to do this ? Thanks in advance Mark Create a table with 2 fields like this: StateID Text (say...

Sheet range reference
I have a workbook with a number of sheets, let's say named sheet01, sheet02 ......sheet10. These sheets are of the same structure and are representing some development in time. So now I would like to make a chart (or separate table) the where the X coordinate is the sheet number and the Y some cell on each of the sheets. Is this possible with a simple reference like the ranges within the same chart or do I really have to create a macro or do it by hand? Jens. Each series must reside on a single sheet. You can create a summary sheet which has something like: A1: =Sheet1!A1 A2: ...

getting error out of date range
i am getting an error out of date range when make an appt on a calander ( sending to someone else the are gettign the error ) ...

SSL Certificate server name is incorrect
I get the message (Exchange server 2003 SP1) The SSL certificate server name is incorrect ID no: c103b404 Exchange System Manager When I try to open the propertie-page of any public folder. Does anyone knows of a way to correct this ? TIA Rudy Steyaert Rudy, I assume this is related to the fact you specified HTTPS / SSL on the overall "default web site" in IIS. If SSL is configured for your Exchange OWA, it's only necessary to force SSL on the Exchange FOlder, not the Public or ExchWeb. As the Public Folder properties in your Exchange system manager are making use of...

how to capture the event changing the name of a sheet
I would like to trigger a procedure whenever a user change the sheet name or add anew sheet. The event of new sheet can be captured but I don't know how to capture the event changing sheet's name. Please help No event is generated when a sheet changes names... What were you needing the event for? Perhaps we can help you get around the problem. -- HTH... Jim Thomlinson "Satyapal Kaushal" wrote: > I would like to trigger a procedure whenever a user change the sheet name or > add anew sheet. The event of new sheet can be captured but I don't...

?-Change data series range as data is entered?
I have a chart which shows a prediction curve and an actual curve. The prediction curve runs out until the end of a project. The actual curve is populated as data is entered. I will be generating a number of these charts from a datatable. I would like to plot the prediction curve for the duration of the project (easy). I would then like to plot the actual curve with only the existing data and not have the line drop to the x-axis at the end. Can I put a formula in the data series "x values" field? I hope that my description makes sense..... Thanks, Carl -- isofuncurves ---...

total of a range of times
This should be simple for some of you, but not for me. I am a runner, I have 4 ind. cells w/my split times. How do I get a total for the tallied times. I'm far from being experienced but am trying to learn..any and all help will be much appreciated. Thanks, Roger Roger To help you in your quest to "try to learn" I will direct you to Chip Pearson's site so's you can learn just about all you'll ever need for Time Calculations. http://www.cpearson.com/excel/datetime.htm#AddingTimes Gord Dibben Excel MVP On Sat, 13 Nov 2004 15:19:02 -0800, "Roger WG" &...

Extracting name out of line
I am copying a line off the web with 4 names in it, separated by spaces (From 1 to 6 spaces). I am trying to extract name 3 from the line. Tried split(Var, " ") however Name moves according to the space between the names. Var(2) can be the Name or according to spaces between names, Var(6) Can you help me? Thank you, Gordon On 27 Jan 2007 15:37:39 -0800, "Gordon" <gwelch1938@yahoo.com> wrote: >I am copying a line off the web with 4 names in it, separated by >spaces (From 1 to 6 spaces). I am trying to extract name 3 from the >line. Tried split(V...

How do I automatically plot different ranges of data in different.
I need to chart parameters that are a function of three variables in the 2002 version of Excel. I have used the x and y axis to represent 2 variables on an 'xy chart', but require an automatic method to represent the value of the third variable. I have tried the 'bubble-plot', but cannot get sufficient resolution between the data. Is their any way that I may plot the third variable in different colours/shades, that automatically changes for different ranges of the the third variable? THis might help you: http://www.peltiertech.com/Excel/Charts/format.html#CondChart &q...

Sheet you want to copy contains the name...
When I copy a worksheet I get the repeated message box that the destination workbook already contains the name "a", "aa", "prntoutline", "ssd", "q" and "zz". I have to select yes to each message box to allow the worksheet copy to complete itself. I do not have these names within the worksheet (chekced by Insert>Name>Define). How can I delete these nsmaes that do not seem to exist? Thanks. Names can be hidden, so Insert=>Name=>Define may not show them. In the troublesome workbook make a blank sheet active, then go...

excel
I am trying to write a macro to save a excel spreadsheet and use a cell that contains text like a customer name. Record your macro to include the File > SaveAs, then edit that line of code. Instead of ... Filename:="\\FinancialReporting\FY2006" ..., you might use .... Filename:="\\FinancialReporting\FY" & range("B2").value ... "bossman tv" wrote: > I am trying to write a macro to save a excel spreadsheet and use a cell that > contains text like a customer name. ...

Getting the longest lengh in range of cells
I am trying to get the length of the longest cell in a range and use the array {=len(a1:a1)}. What happens is that it picks up the length of cell A1. Is there a way in a formula to determine the longest cell length in a range, or through VBA. Thanks in advance Hi Try this formula array (please amend with last row number): {=MAX(LEN(A1:A7))} HTH Cordially Pascal "Geoff" <gh@bob.com> a �crit dans le message de news:uMKoCQiSFHA.1176@TK2MSFTNGP10.phx.gbl... > I am trying to get the length of the longest cell in a range and use the > array {=len(a1:a1)}. What happen...

Parsing Names in Excel
I have a list in Excel from A1:A100 with names like the ones mentioned below: Michelle Fooo (michelfoo@exchange.SASMOM.com) Myhaass Smith (mysmith@exchange.SASMOM.com) I want to parse them using string functions to the following format michelfoo@SASMOM.com mysmith@SASMOM.com Any ideas how to do this using a formula? Thanks in advance Michael Michael Are you wanting to strip all the name out too or just remove the exchange.? If the latter just use edit>replace What:=exchange. and leave the with blank -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_...

wrong display name
Why is my messenger displaying my name from web from my Live profile??? I've never checked any box that allows to take name from web. I want to use my custom name that I set in my messenger client. And if I wanted to change my name in Live profile in home.live.com then it refuses to accept special characters that I've been using for years in my messenger client. There is something broken, you know, and please fix it as soon as possible. Thanks! Greetings, There are instances where it can change your name to what's in your live profile in Messenger, but you c...

Select Range
Having trouble selecting several non-contiguous ranges. The spreadsheet I am working with is apprx. 300 rows by 110 columns. The sheet is subtotaled. I need to copy data and move to a summary worksheet. There is a subtotaled section entitled 'Risk'. Within this section, the only data required is Country, Customer, 3Q (orders, revenue, gross margin), and 4Q (orders, revenue, gross margin); columns B, E, AQ, AR, AS, BK, BL, and BM - respectively. The section title is in cell A162 and the data is in A163:CY200. I would prefer to select all of the data from the section at once, but ...

Iterate 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in mes...