Remove blank cells from a range

If I have a range of cells, what worksheet function can I use to create
a new range that has all the values from the previous range, but with
all blanks removed. I need this for a data validation formula - I want
the data validation dropdown to only show nonblank values from a range.

Thanks!

- Dave

Select the range;Use Data | Filter | AutoFilter, specify non-blanks
copy and paste as needed
Select the original range, Use Data | Filter | and uncheck AutoFilter
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave" <[email protected]> wrote in message
news:[email protected]...
> If I have a range of cells, what worksheet function can I use to create
> a new range that has all the values from the previous range, but with
> all blanks removed. I need this for a data validation formula - I want
> the data validation dropdown to only show nonblank values from a range.
>
> Thanks!
>
> - Dave
>

Thanks, but I need a worksheet function to do this. The resulting
range is what I'll use as a list validation formula for data validation
on another cell. It's pretty complicated to explain in detail what I'm
trying to do, but basically - I need a worksheet function that will
return a range that has all the blank values removed from another range.