Data validation and empty cells

K

Kris

range("d1").Validation.add formula1:=
"=$A$1:$A$7",Type:=xlValidateList,operator:=xlBetween


How to avoid empty entries in drop down box if some of cell from A1:A7
are empty?

Thanks
 
B

Bob Phillips

Sort A1:A7 so that the empties are at the bottom and use

=OFFSET($A$1,,,COUNT($A$1:$A$7),1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top