I am trying to figure out if there is a way to merge 2 named ranges into one.
For example:
Range1 = $A$65514:$E$65536
Range2 = $K$3:$O$68
I need to create 1 Named range which will contain both selections. Any help is greatly appreciated.
Merge 2 Named Ranges
I am trying to figure out if there is a way to merge 2 named ranges into one.
For example:
Range1 = $A$65514:$E$65536
Range2 = $K$3:$O$68
I need to create 1 Named range which will contain both selections. Any help is greatly appreciated.
what do you mean contain?
Do you mean in a list of some kind?
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
I'm not sure what your trying to do but I found this hidden away. Don't know where I got it.
[VBA]
Dim rng As Range
Set rng = Union(Range("H:J"), Range("P:P"), Range("A:A"), Range("3:3"), Range("5:5"))
If Intersect(Target, rng) Is Nothing Then Exit Sub
MsgBox "Hit"
[/VBA]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
By contain I mean, 1 named range which will include all the cells from range1 and all the cells from range2.
Hi Innany,
Named Ranges can quite happily contain Multiple Selections.
Select the first Range as normal (A65514:E65536), then hold down CTRL and select the second Range (K3:O68) - then type the Name in the Name Box as you would do normally.
I know i can select that manually, I need to do it via VBA code and combine 2 existing name ranges, which are already defined.
Sorry, I misunderstood ....
Try:
[VBA]
ActiveWorkbook.Names.Add Name:="NewName", RefersTo:= _
"=Range1,Range2"
[/VBA]
Where:
> NewName is the named range being created
> Range1 is an existing Named Range
> Range2 is an existing Named Range
This will create the named range for you, and you CAN then reference the 'NewName' range as normal in further VBA - BUT - what I have noticed is that the new named Range is not visible in the Name select box, or on the GoTo function.
Regards,
Apps
Thank you, I will try that
This works great, when I explicitly put the names of named ranges into the code above. If I use variables though, I can't get it to work. Any special syntax I should use?
Can you post the code you're trying to use?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Here it is:
It does create d range fine, but the merging does not work.
VBA:
[[VBA]If RangeExist(c) = 1 Then
d = c & 2
e = c & 3
ActiveWorkbook.Names.Add Name:=(d), RefersTo:="='RAW DATA'!" & RTE.Address
ActiveWorkbook.Names.Add Name:=(e), RefersTo:="=(c),(d)"
Else
ActiveWorkbook.Names.Add Name:=(c), RefersTo:="='RAW DATA'!" & RTE.Address
End If][/VBA]
BTW: how do you use the vba tags?
Last edited by Innany; 06-13-2006 at 10:17 AM.
Hi Innany
If your variables c and d are ranges, you'll need to get these addresses and incorporate them into the string which becomes the RefersTo object eg [vba]ActiveWorkbook.Names.Add Name:=(e), RefersTo:="=" & c.address, & "," & d.address
[/vba]
You add the VBA tags by highlighting your cpode and clicking the VBA button.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
That does not work at all, before it would create the name range, now it does not do that at all, it actually exits out of the sub on that line.
I also had to remove the comma after c.address , otherwise it would not compile
Don't know if this helps, but:
Set RTE = ActiveWindow.RangeSelection
and RTE is declared as range
c is not declared as range, originally it is just a string, but then the range is create with that name
Try
[VBA]Sub MakeRanges()
Dim RTE As Range, c As Range, d As Range, e As Range
Dim f As String
Set RTE = Range("A1:A10")
ActiveWorkbook.Names.Add Name:="Test1", RefersTo:="='RAW DATA'!" & RTE.Address
ActiveWorkbook.Names.Add Name:="Test2", RefersTo:="='RAW DATA'!" & RTE.Offset(, 2).Address
ActiveWorkbook.Names.Add Name:="Test3", RefersTo:="='RAW DATA'!" & RTE.Offset(, 4).Address
Set c = Range("Test1")
Set d = Range("Test2")
Set e = Range("Test3")
f = "Test4"
ActiveWorkbook.Names.Add Name:=f, RefersTo:="=" & _
c.Address & "," & d.Address & "," & e.Address
For Each n In ActiveWorkbook.Names
msg = msg & n.Name & " - " & n.RefersToRange.Address & vbCr
Next
MsgBox msg
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
BTW,
C and (C) are not valid for use as range names
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Why can't c be used? I guess I can change that to a different letter. The problem is as I mentioned above, I cannot declare c as range, otherwise the rest of my code won't work.
This is where I get c from:
[VBA]For i = 1 To Row
c = WorksheetFunction.Index(Range("AppServers"), i)[/VBA]
Try adding c as a name using the Insert Names routine.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Sorry, not sure what are you reffering to?
This method.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
That is exactly what I am trying to avoid, adding anything manually, also c changes with every loop itteration, and on every itteration it creates a new name range with a different name (names are being fed from a range on the sheet)
Posting Permissions
Reply With Quote
