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)

"Kris" wrote in message
...
range("d1").Validation.add formula1:=
"=$A$1:$A$7",Type:=xlValidateList,operator:=xlBetw een


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

Thanks