Hi,

Try this :

This procedure should clear all data
define with "Name" on Sheet2

'---------------------------------------------
Sub Clear_Range()
Dim T As String, N As Name, X As Variant

With Worksheets("Sheet2") 'Adapte name sheet
For Each N In ActiveWorkbook.Names
X = Split(N.RefersTo, ",")
For a = 0 To UBound(X)
T = X(a)
If Left(N.RefersTo, 2) = "=!" Then
T = .Name & "!" & Replace(N.RefersTo, "=!", "")
End If
If Range(T).Parent.Name = .Name Then
Range(T).ClearContents
End If
Next
Next
End With
End Sub
'------------------------------------------



"BRC" a écrit dans le message de groupe de discussion :
...
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