Data Validation and Name Range
 

To All,
I have a named range that includes some blank cells that I am trying
to eliminate from a drop down list on another worksheet. I have Googled
the internet and searched through several Excel newsgroups and think the
solution is the use of INDIRECT and Dynamic ranges, except using COUNTA
stops at the first blank cell.

Can someone point me in the right direction with either a Formula or
VBA that will take a named range (I would like a Dynamic range - if
possible), strip out the blank cells to use with a drop down data
validation list.

Thanks
Harry

Data Validation and Name Range
 

Are you using Data Validation drop down lists? Because you can click the Do
not ignore blanks box.

"Harry Stevens" wrote:

To All,
I have a named range that includes some blank cells that I am trying
to eliminate from a drop down list on another worksheet. I have Googled
the internet and searched through several Excel newsgroups and think the
solution is the use of INDIRECT and Dynamic ranges, except using COUNTA
stops at the first blank cell.

Can someone point me in the right direction with either a Formula or
VBA that will take a named range (I would like a Dynamic range - if
possible), strip out the blank cells to use with a drop down data
validation list.

Thanks
Harry

Data Validation and Name Range
 

Using a dynamic range will find the *last* non-empty cell in a range of
cells. If you have empty cells *within* that range a dynamic range will
include those empty cells. The only way to eliminate those empty cells
*within* the range is to create another list of contiguous values.

See if this helps:

http://contextures.com/xlNames01.html#Dynamic

Biff

"Harry Stevens" wrote in message
...
To All,
I have a named range that includes some blank cells that I am trying to
eliminate from a drop down list on another worksheet. I have Googled the
internet and searched through several Excel newsgroups and think the
solution is the use of INDIRECT and Dynamic ranges, except using COUNTA
stops at the first blank cell.

Can someone point me in the right direction with either a Formula or VBA
that will take a named range (I would like a Dynamic range - if possible),
strip out the blank cells to use with a drop down data validation list.

Thanks
Harry