Add two Named Ranges in one ComboBox control

[pipoliveira]

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

[6StringJazzer]

You can repeat the technique you used for DU:

Please Login or Register  to view this content.

[MickG]

Try this:-

Please Login or Register  to view this content.

[mikerickson]

With two different ranges, presumably separate, you might want to add a hidden column to the combo box so you can tell where the data came from

Please Login or Register  to view this content.

Also, I'm wondering about this being in the DropButtonClick event. In the Userform_Intialize event is usually where one loads a combo box. If the dropbuttonClick is where it needs to go, there should be a line clearing the combo box before it is filled (or double filled),

[pipoliveira]

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.

[6StringJazzer]

Actually, here is one more that is a more elegant than my initial. However, I won't blame you if you quit while you're ahead :-)

<to avoid confusion, removed code containing one error and one possibly incorrect assumption>

[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.

Please Login or Register  to view this content.

[6StringJazzer]

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.

[pipoliveira]

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

[6StringJazzer]

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.

[pipoliveira]

That's it! It solved the problem.
You're a star. Very appreciated.
Best regards,
Filipe


Sent from my iPhone using Tapatalk