PDA

View Full Version : Solved: Combining Dynamic Ranges



Ethan
09-18-2011, 12:40 AM
Hello,

To fill a combobox I use the following formula as dynamic range.
Is it possible however to add a second dynamic range to it?



=OFFSET(Strt!$I$5;0;0;COUNTA(Strt!$I:$I);1)
=OFFSET(Strt!$L$5;0;0;COUNTA(Strt!$L:$L);1)

Bob Phillips
09-18-2011, 02:16 AM
What is your code to add it, do you set the List property or RowSource?

Ethan
09-18-2011, 02:50 AM
Hello xld,

I use it as a named range and place it in RowSource

Bob Phillips
09-18-2011, 03:47 AM
As I feared.

If you do want to use this method, you could create a function that builds an array from both, like so



Function fxUnion(ParamArray rng()) As Variant
Dim ary As Variant
Dim cell As Range
Dim cnt As Long
Dim i As Long
ReDim ary(1 To 1)
For i = LBound(rng) To UBound(rng)

For Each cell In rng(i)

cnt = cnt + 1
ReDim Preserve ary(1 To cnt)
ary(cnt) = cell.Text
Next cell
Next i
fxUnion = ary
End Function
and load the combo in the Initialise of the form

Me.ComboBox1.List = fxUnion(Range("_rng1"), Range("_rng2"))

Ethan
09-18-2011, 06:18 AM
Awesome xld, it works great! :rotlaugh:

Bob Phillips
09-18-2011, 06:20 AM
BTW, that function allows for any number of ranges to be passed to it.

Ethan
09-18-2011, 06:28 AM
BTW, that function allows for any number of ranges to be passed to it.

Great! thanks again for your help