Removing blanks from a drop down

[darwinquintero]

Hi guys I've inserted a drop down list in my spreadsheet, and I am getting (as well as the information I desire), blanks when I click the drop down, does anyone know how to remove these blanks? It is imperative that I do not remove the cells highlighted in the range.

Thank you in advance for any answers :D

 

[jim may]

By what means have you inserted the drop down?

1) Data Validation
2) Forms ComboBox
3) Active-X ComboBox

 

[darwinquintero]

By what means have you inserted the drop down?

1) Data Validation
2) Forms ComboBox
3) Active-X ComboBox

Data validation pal

 

[jim may]

So, the obvious question.. Whay are there blanks? Are you using within your Data validation List and in the Source have entered "=MyList" (without the quote marks)?
Is MyList a valid Named range, including the Blank rows? Does this range (MyList) get overwritten with new data periodically form some update process?

 

[CWatts]

You can use OFFSET in your defined names if the blanks aren't scattered throughout your list and are only at the end.

For example, if you have a range of cells A1:A10 and only the first 5 are filled out, and you don't want blanks at the end, make this your named range. It basically says it starts in cell A1, and counts the number of cells that aren't blank and expands the range based on that result.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$10),1)

Here's a YouTube video that may help illustrate it, too.

http://www.youtube.com/watch?v=2UDRm-hU0Jw