Selecting Named Ranges

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

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
 
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
 
This causes an error for me.

how can a range be the loop object for the collection of names?
 
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
 
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
 
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.
 
Back
Top