Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reference a Range of cells in excel using variable names | Excel Programming | |||
Variable Names Range - Help Needed | Excel Programming | |||
Range("C100:D200").Select with variable names | Excel Programming | |||
Variable names for named range | Excel Discussion (Misc queries) | |||
'ActiveWorkbook.Names.Add Name:' how to make range variable? | Excel Programming |