I has a worksheet with approx 20 separate named ranges. I
wish to clear the contents of all these ranges through VBA
in the most efficient way.

Is there a way of selecting all the named ranges within a
specifc worksheet so that I can apply ClearContents to all
the ranges at once.

Thanks.

I guess you could build a union of them if you had a list. If you don't,

Dim rng as Range
Dim rng1 as Range
Dim nm as Name
Dim sh as Worksheet
set sh = Worksheets(1)
for each nm in Thisworkbook.Names
set rng = nothing
on Error Resume Next
set rng = nm.ReferstoRange
On Error goto 0
if not rng is nothing then
if rng.parent.name = sh.name then
if rng1 is nothing then
set rng1 = rng
else
set rng1 = union(rng,rng1)
end if
End if
End if
Next
if not rng1 is nothing then
rng1.clearcontents
End if

--
Regards,
Tom Ogilvy

"Ian" wrote in message
...
I has a worksheet with approx 20 separate named ranges. I
wish to clear the contents of all these ranges through VBA
in the most efficient way.

Is there a way of selecting all the named ranges within a
specifc worksheet so that I can apply ClearContents to all
the ranges at once.

Thanks.

Ian,

How about just

Sub ClearRange()
Dim rng As Range
Dim sh As Worksheet

Set rng = Range("bob", "alan")
Set sh = Worksheets("Sheet1")
If Not rng Is Nothing Then
If rng.Parent.Name = sh.Name Then
rng.ClearContents
End If
End If
End Sub

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Ian" wrote in message
...
I has a worksheet with approx 20 separate named ranges. I
wish to clear the contents of all these ranges through VBA
in the most efficient way.

Is there a way of selecting all the named ranges within a
specifc worksheet so that I can apply ClearContents to all
the ranges at once.

Thanks.

Hi,



I'm still slipping this morning. Here the correction:



Sub DelName()

Dim nmA As Name

For Each nmA In ActiveWorkbook.Names

If ActiveSheet.Name = Mid(nmA.RefersToLocal, 2,
InStr(nmA.RefersToLocal, "!") - 2) Then

nmA.RefersToRange.Value = ""

End If

Next nmA

End Sub


--
JP

http://www.solutionsvba.com


"Ian" wrote in message
...
I has a worksheet with approx 20 separate named ranges. I
wish to clear the contents of all these ranges through VBA
in the most efficient way.

Is there a way of selecting all the named ranges within a
specifc worksheet so that I can apply ClearContents to all
the ranges at once.

Thanks.

Here's just another general idea...

Sub Demo()
Dim nme As Name
Const ClearNamesOnThisSheet As String = "Sheet1"

For Each nme In ActiveWorkbook.Names
If StrComp( _
nme.RefersToRange.Parent.Name, _
ClearNamesOnThisSheet, _
vbTextCompare) = 0 Then
Range(nme).ClearContents
End If
Next nme
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Ian" wrote in message
...
I has a worksheet with approx 20 separate named ranges. I
wish to clear the contents of all these ranges through VBA
in the most efficient way.

Is there a way of selecting all the named ranges within a
specifc worksheet so that I can apply ClearContents to all
the ranges at once.

Thanks.

There is no guarantee that ReferstoRange will be defined for all names. If
you don't account for that, you can get an error.

Of course in this user's case, that may not be an issue.

--
Regards,
Tom Ogilvy

"Dana DeLouis" wrote in message
...
Here's just another general idea...

Sub Demo()
Dim nme As Name
Const ClearNamesOnThisSheet As String = "Sheet1"

For Each nme In ActiveWorkbook.Names
If StrComp( _
nme.RefersToRange.Parent.Name, _
ClearNamesOnThisSheet, _
vbTextCompare) = 0 Then
Range(nme).ClearContents
End If
Next nme
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Ian" wrote in message
...
I has a worksheet with approx 20 separate named ranges. I
wish to clear the contents of all these ranges through VBA
in the most efficient way.

Is there a way of selecting all the named ranges within a
specifc worksheet so that I can apply ClearContents to all
the ranges at once.

Thanks.