Add two Named Ranges in one ComboBox control

Dear All,

I hope you are all well.

I have two NamedRanges that are lists of different departments.

What I need, is to have a ComboBox with both NamedRanges merged on the Drop Down list.

The code I have, it lists one of the named ranges and hides the cells market as "Empty" as follow:

Please Login or Register  to view this content.

As you can see, one of the named ranges is "DU" and I need to add another into the list (named range as "FU").

Is it possible to "merge" it into one ComboBox?

Your assistance on this matter will be highly appreciated.

Best regards,
Filipe

Try this:-

Please Login or Register  to view this content.

Thanks guys,

It worked perfectly.

I have used the reply from 6StringJazzer as it was the first reply and it worked as desired.

I will keep the other tips for the future.

Once again, thanks a million for your precious help. Really appreciated.

Filipe Oliveira.

Originally Posted by mikerickson

Combining the names inside one statement will error if the named ranges are on different sheets.
Also, the .Columns property of a discontinuous range acts oddly for Excel 2013.
The loop omits the cells in the second Area of the combined range.

True on both counts. I had not done any significant testing (OP should attach a file).

On the second point, this is not specific to 2013. The Columns(1) will select the first column of the entire range, not each named range individually. Therefore only the leftmost column of the entire combined range is used.

Therefore I retract my second suggestion.

Thanks guys.

@6StringJazzer, I have tried initially to add the two names ranges as "With Range("DU, FU")" but it didn't worked.

Although it is working well now, I am facing a small issue. Not sure If I do have to create a new threat for it.

Now, if I click the drop down menu, close and open again, it keeps adding and duplicating the values on the ComboBox menu.

Any ideas? As I mention, maybe I need to create a new threat.

Thanks a million in advance.

Kind regards,
Filipe

In in the posts above you will see that if DU and FU are on different sheets, my second solution won't work, and I have removed it. Stick with the first one, or other solutions presented here as you prefer.

To solve your latest problem add this line before the first With statement:

Please Login or Register  to view this content.

Do the values in DU and FU change? If they never change, then you should load the combobox in the UserForm_Activate sub, rather than doing it every time the user clicks on the combobox.