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" wrote in message
ups.com...
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.