You may find that you want to limit the range names to clearcontents. Someday
you may find that you add a name and run this macro and something now unintended
occurs.

Option Explicit
Sub ClearContentsOfSomeNames()

Dim myName As Name
Dim TestRng As Range

For Each myName In ActiveWorkbook.Names
With myName
If LCase(.Name) Like LCase("*!_FilterDatabase") _
Or LCase(.Name) Like LCase("*!Print_Area") _
Or LCase(.Name) Like LCase("*!Print_Titles") _
Or LCase(.Name) Like LCase("*wvu.*") _
Or LCase(.Name) Like LCase("*wr.*") _
Or LCase(.Name) Like LCase("*!Criteria") Then
'skip it
Else
Set TestRng = Nothing
On Error Resume Next
Set TestRng = myName.RefersToRange
On Error GoTo 0

If TestRng Is Nothing Then
'name doesn't refer to a range
Else
If LCase(TestRng.Parent.Name) = LCase("Sheet2") Then
TestRng.ClearContents
End If
End If
End If
End With
Next myName
End Sub



BRC wrote:

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

--

Dave Peterson