Combining named ranges into one Master Range for validation

gvanbee

New Member
Joined
Jun 26, 2002
Messages
42
Hello:

I have searched the archive, and the net, and can not find a solution to my problem. I know someone on this board knows a solution.

I have several different named ranges that are used for validating a column. I need to provide in a seperate column a validation that combines all of the seperate ranges into one.

NamedRange1 = a1:a3 {a1,a2,a3}
NamedRange2 = a4:A6 {a4,a5,a6}

where a1, a2, a3... are references to the cells that are the value.

What I need is the syntax to define a new range name such as:

CombinedRange = NamedRange1 + NamedRange2
which would not be a mathmatical addition, but a combined list such as:

CombinedRange = A1:A6 {a1, a2, a3, a4, a5, a6}
This message was edited by gvanbee on 2002-10-02 14:13
This message was edited by gvanbee on 2002-10-02 14:14
This message was edited by gvanbee on 2002-10-02 14:18
 
Example: {=combine_arrays("A1:B7","B1:B3","C1:C4")}
Or for(dynamic) named ranges: {=combine_arrays("data1","data2","data3")}

Avoid using "A1:B1" as this is reserved as a default and is ignored.

Remember the function returns an array so use Ctrl-Shift-Enter. Currently combines between 2 and 4 ranges/arrays but could be modified to allow more.

Enjoy :)

Public Function combine_arrays(aa As String, bb As String, _
Optional cc As String = "A1:B1", _
Optional dd As String = "A1:B1") As Variant()
a = Range(aa)
b = Range(bb)
c = Range(cc)
d = Range(dd)

If UBound(a, 2) <> UBound(b, 2) Then

combine_arrays = Array("Arrays must have the same number of columns")

Else

Dim x() As Variant

result_array_size = (UBound(a, 1) + UBound(b, 1) - 1) + _
(UBound(c, 1) - 1) + _
(UBound(d, 1) - 1)

ReDim x(result_array_size, UBound(a, 2) - 1)

For Row = 1 To UBound(a, 1)
For col = 1 To UBound(a, 2)
x(Row - 1, col - 1) = a(Row, col)
Next col
Next Row

For Row = 1 To UBound(b, 1)
For col = 1 To UBound(b, 2)
x(Row + UBound(a, 1) - 1, col - 1) = b(Row, col)
Next col
Next Row

If cc <> "A1:B1" Then
For Row = 1 To UBound(c, 1)
For col = 1 To UBound(c, 2)
x(Row + UBound(a, 1) + UBound(b, 1) - 2, col - 1) = c(Row, col)
Next col
Next Row
End If

If dd <> "A1:B1" Then
For Row = 1 To UBound(d, 1)
For col = 1 To UBound(d, 2)
x(Row + UBound(a, 1) + UBound(b, 1) + UBound(c, 1) - 3, col - 1) = d(Row, col)
Next col
Next Row
End If

combine_arrays = x

End If

End Function
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I have entered your UDF into excel but find that it does not work. I wonder if you are available to trouble shoot as I am not very experienced with VBA.
Thanks
M
 
Upvote 0
RoyalFlesh - did you get a reply or a solution? I have the same problem!

Thanks

I also am searching for an answer to this, without using VBA. My ranges consist of dates.
Anyone have this answer?
 
Upvote 0
If anyone still needs an answer on how to combined named ranges here is a webpage that explains how to accomplish this with a formula. No VBA needed.

 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top