Combobox add to list - two ranges

[neodjandre]

I am using this code:

Dim lastrow As Integer
Dim lastrow2 As Integer
Dim adrow As Integer
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range

adrow = x_admin.Range("range_admin_header").Row + 1
lastrow = Application.WorksheetFunction.CountIf(x_admin.Range("J" & adrow & ":" & "J245"), "1") - 1
lastrow2 = Application.WorksheetFunction.CountIf(x_admin.Range("L" & adrow & ":" & "L245"), "1") - 1

Set rng1 = x_admin.Range("I" & adrow & ":" & "I" & adrow + lastrow)
Set rng2 = x_admin.Range("K" & adrow & ":" & "K" & adrow + lastrow2)
Set rng3 = Union(rng1, rng2)

query_combo.List = rng3.Value2

query_combo is a VBA combobox. But for some weird reason the combobox only lists rng1 values instead of the Union values with rng2.

 

[Fluff]

How about

Dim lastrow As Integer
Dim lastrow2 As Integer
Dim adrow As Integer
Dim Ary1 As Variant, Ary2 As Variant, Ary3 As Variant
Dim r As Long, nr As Long

adrow = x_admin.Range("range_admin_header").Row + 1
lastrow = Application.WorksheetFunction.CountIf(x_admin.Range("J" & adrow & ":" & "J245"), "1") - 1
lastrow2 = Application.WorksheetFunction.CountIf(x_admin.Range("L" & adrow & ":" & "L245"), "1") - 1

Ary1 = x_admin.Range("I" & adrow & ":" & "I" & adrow + lastrow).Value2
Ary2 = x_admin.Range("K" & adrow & ":" & "K" & adrow + lastrow2).Value2

ReDim Ary3(1 To UBound(Ary1) + UBound(Ary2))
For r = 1 To UBound(Ary1)
   nr = nr + 1
   Ary3(nr) = Ary1(r, 1)
Next r
For r = 1 To UBound(Ary2)
   nr = nr + 1
   Ary3(nr) = Ary2(r, 1)
Next r

query_combo.List = Ary3

 

[neodjandre]

hmm ok, I will try this, though I was trying to avoid looping

 

[Fluff]

Because it's looping through arrays it will be very quick.

 

[neodjandre]

ok perfect it works just fine ! many thnks

 

[Fluff]

You're welcome & thanks for the feedback.