PDA

View Full Version : Merge 2 Named Ranges



Innany
05-31-2006, 01:13 PM
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.

XLGibbs
05-31-2006, 03:48 PM
what do you mean contain?

Do you mean in a list of some kind?

lucas
05-31-2006, 03:49 PM
I'm not sure what your trying to do but I found this hidden away. Don't know where I got it.

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"

Innany
05-31-2006, 06:10 PM
By contain I mean, 1 named range which will include all the cells from range1 and all the cells from range2.

Apps
06-01-2006, 05:43 AM
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.

Innany
06-01-2006, 07:43 AM
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.

Apps
06-01-2006, 08:38 AM
Sorry, I misunderstood ....

Try:


ActiveWorkbook.Names.Add Name:="NewName", RefersTo:= _
"=Range1,Range2"


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

Innany
06-01-2006, 09:11 AM
Thank you, I will try that

Innany
06-13-2006, 06:07 AM
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?

mdmackillop
06-13-2006, 08:42 AM
Can you post the code you're trying to use?

Innany
06-13-2006, 09:02 AM
Here it is:

It does create d range fine, but the merging does not work.

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]

BTW: how do you use the vba tags?

mdmackillop
06-13-2006, 10:12 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 ActiveWorkbook.Names.Add Name:=(e), RefersTo:="=" & c.address, & "," & d.address

You add the VBA tags by highlighting your cpode and clicking the VBA button.

Innany
06-13-2006, 10:56 AM
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

mdmackillop
06-13-2006, 11:12 AM
Try
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

mdmackillop
06-13-2006, 11:14 AM
BTW,
C and (C) are not valid for use as range names

Innany
06-13-2006, 11:29 AM
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:

For i = 1 To Row

c = WorksheetFunction.Index(Range("AppServers"), i)

mdmackillop
06-13-2006, 11:40 AM
Try adding c as a name using the Insert Names routine.

Innany
06-13-2006, 11:56 AM
Sorry, not sure what are you reffering to?

mdmackillop
06-13-2006, 12:19 PM
This method.

Innany
06-13-2006, 12:46 PM
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)

mdmackillop
06-13-2006, 12:52 PM
Why not post your code. I've no idea what are strings, names and variables in your posted snippets.