Solved: Combining Dynamic Ranges

[Ethan]

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?


[VBA]
=OFFSET(Strt!$I$5;0;0;COUNTA(Strt!$I:$I);1)
=OFFSET(Strt!$L$5;0;0;COUNTA(Strt!$L:$L);1)[/VBA]

[Bob Phillips]

What is your code to add it, do you set the List property or RowSource?

[Ethan]

Hello xld,

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

[Bob Phillips]

As I feared.

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

[vba]

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[/vba]
and load the combo in the Initialise of the form

[vba] Me.ComboBox1.List = fxUnion(Range("_rng1"), Range("_rng2"))
[/vba]

[Ethan]

Awesome xld, it works great!

[Bob Phillips]

BTW, that function allows for any number of ranges to be passed to it.

[Ethan]

BTW, that function allows for any number of ranges to be passed to it.

Great! thanks again for your help