Remove blank cells from a range

D

Dave

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
 
B

Bernard Liengme

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
 
D

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.
 
Top