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

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

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

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.

BRC wrote:

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

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

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

"BRC" wrote in message
...
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

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

On Jan 10, 1:34*pm, "Don Guillett" wrote:
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
"BRC" wrote in message

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

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

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

"BRC" wrote in message
...
On Jan 10, 1:34 pm, "Don Guillett" wrote:
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
"BRC" wrote in message

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

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

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

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

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



BRC wrote:

On Jan 10, 1:34 pm, "Don Guillett" wrote:
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
"BRC" wrote in message

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

--

Dave Peterson