I did test before sending.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BRC" wrote in message
...
On Jan 10, 1:34 pm, "Don Guillett" wrote:
Based on what Rick gave you

Sub DeleteNamesFromActiveSheet()
For Each N In ActiveWorkbook.Names
If N.RefersToRange.Parent.Name = "Sheet2" _
Then Range(N).ClearContents
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"BRC" wrote in message

...
Good afternoon
I am trying to loop thru all ranges on a particular worksheet (sheet2)
and execute the command Range("loopthruallranges").ClearContents. the
clearcontents was provided by MVP Rick and it does exactly what i want
to do but my problem is resolving the names of the individual ranges
so that “loopthruallranges” can be replaced by a variable. I believe
it has to be something like

Sub clearcontents()
dim rn as string
for each range in worksheet (sheet2) range.
x=range.name
x.clearcontents
Next
end sub

But I have tried a multitude of different schemes and I get odd
errors. I have also notice active sheet can have impact on range names
that are enumerated. Any help is appreciated.
BRC

Thank you for the responses. No, I am not using any reoccuring
prefixes in names. I tried Don's code and found that I can get it to
work if I explicitly activate sheet2 and then replace "sheet2" in the
code with "activesheet.name". Not sure why "sheet2" doen't work. It
would seem to me that these would be an equivelnt expressions in this
context. thanks again for the help BRC