ComboBox ListFillRange without the blank rows in the range

[coatsy35]

Hi,

I have an excel spreadsheet which has two activeX ComboBox's on it. The selection of the 1st ComboBox determines the ListFillRange of the second ComboBox by referencing named ranges called "Yes" and "No", when "Yes" is selected the 2nd ComboBox changes it's contents to the named range "YesServicePlus" and when "No" is selected it fills the contents with a named range called "NoServicePlus".

The problem is, my named ranges are entire columns, as the contents of these ranges are updated regularly by inexperienced excel operators! this results in a lot of whitespace/blank rows at the end of the combobox list. Is there a way to removed this and only insert a ListFillRange with rows that actually have a value in them? meaning I don't have to specify the exact rows with data in them as my named range.

My VBA coce to fill the 2nd ComboBox is:


Code:
XtracareComboBox.ListFillRange = ComboBox1.Value
Any help appreciated.

Dan

 

[NoS]

You don't allow much time for an answer before posting same thing on other sites do you ?

Believe you should ask Google about dynamic named ranges.

Myself, would probably use a table and refer to the table column.

 

[coatsy35]

Never knew about Dynamic ranges, That has fixed it thank you.

 

[kadriguler]

I recommend you to review this tutorial (it is about dependent combo boxes - creating dynamic range names) :

Link to video site removed by moderator.

 

[AliGW]

Kadriguler - we would prefer you to give advice here within the thread rather than simply supplying links to external sites. Users will understandably be wary about following these, and it is beginning to look like advertising in your case, which is against the spirit of this forum. If you have advice that will be useful to the OP, then please explain that here. By all means attach a sample working file if you wish, but no more links to external videos, please.

Thanks for your cooperation and understanding.

 

[p45cal]

You don't allow much time for an answer before posting same thing on other sites do you ?

Coatsy, please read the rules on the sites you post your question on.
If you must cross post to get a quicker answer/get more people to look at your question, it is a requirement that you provide links to those cross posts, not just a request. Pretty much all such forums have the same rules on this.
Cross posted without links here:
http://www.excelforum.com/showthread.php?t=1170518

If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184