Selecting Named Ranges
 

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.

Selecting Named Ranges
 

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.

Selecting Named Ranges
 

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.

Selecting Named Ranges
 

This causes an error for me.

how can a range be the loop object for the collection of names?

--
Regards,
Tom Ogilvy

"Jean-Paul Viel" wrote in message
...
Hi,
It should do the work:

Sub DelName()
Dim rg As Range

For Each rg In ActiveWorkbook.Names
If ActiveSheet.Name Like "*" & rg.Address & "*" Then
rg.ClearComments
End If
Next rg
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.

Selecting Named Ranges
 

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.

Selecting Named Ranges
 

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.

Selecting Named Ranges
 

I provided code that will do that. Do you not see it?

--
Regards,
Tom Ogilvy


"Ian" wrote in message
...
Bob,

Thanks for your suggestion...

Does this option not require all the named ranges to be
listed in you intital "Set" statement?

I have over 20 named ranges in the particular sheet and
want all of them cleared. I was hoping to find some code
to clear all of these without having to list them all
explicitly.

Ian

-----Original Message-----
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.




.

Selecting Named Ranges
 

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.