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