trying to resolve range names to variable that vba can act on

[BRC]

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

 

[Dave Peterson]

There are a few names that excel uses that would damage your data if you ran
your code.

Instead, I'd make sure I was only processing the correct names. Did you use any
standard naming convention--starting with a common prefix???

BRC_Range1
BRC_Range2
....

If no, you could be explicit in the names you want to clear.

 

[Don Guillett]

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
(e-mail address removed)
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

 

[BRC]

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

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

 

[Don Guillett]

I did test before sending.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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

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

 

[michdenis]

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" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
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

 

[Dave Peterson]

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