Hello,

I have a problem utilizing the Data Validation tool in Excel. If I
have a list in one column with the following, for example:

-----------------
Dog
Cat
Rabbit

Frog
Kangaroo
-----------------

See how the list is really six items long? However, if I use a data
validation on a separate cell with a List referring to the above range
of animals, the validation list shows the exact same list:

-----------------
Dog
Cat
Rabbit

Frog
Kangaroo
-----------------

However, how could I fix this without using "Sort" so that the data
validation list in the cell shows the following:

-----------------
Dog
Cat
Rabbit
Frog
Kangaroo
-----------------

Where the rows are only populated by nonblank values. This is
especially handy when my real range is say hundreds of rows long with
many blank cells inbetween the beginning and the end of the range.
I've seen a lot of other forums where people have suggested using the
Offset function; however, that only works to dynamically cut off the
range when there are blanks following the nonblank cells. It does NOT
work for ranges with intermittent blanks.

Thanks,
D