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.

 

[diddi]

query_combo.List = rng3.Value2 delete that "2"

 

[neodjandre]

Hi, this suggestion doesn't solve the problem. The value2 is just an option to avoid lookup of formatting.

 

[Fluff]

How about

query_combo.List = Application.Index(rng1.Resize(, 3).Value2, Evaluate("row(1:" & rng1.Rows.Count & ")"), Array(1, 3))

 

[neodjandre]

Hi @Fluff this code works only for rng1. I am looking for the query_combo to concatenate rng1 and rng2 range values. I am still confused as to why the 'Union' in my code doesn't work as expected

 

[Fluff]

Is your combo set to show 2 columns?

 

[neodjandre]

No only one column

 

[Fluff]

Then you will need to change the property if you want to show 2 columns

 

[neodjandre]

But I only want to have 1 column with the values of rng2 being appended below the values of rng1. Perhaps I misunderstood the functionality of Union ?

 

[Fluff]

Perhaps I misunderstood the functionality of Union ?

Yes you did, your Union will return two columns, not one.