Data validation and empty cells

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

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" <witek7205@spam.gazeta.pl.invalid> wrote in message
news:do75sd$8a4$1@inews.gazeta.pl...
> 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

Bob Phillips wrote:
> Sort A1:A7 so that the empties are at the bottom and use
>
> =OFFSET($A$1,,,COUNT($A$1:$A$7),1)
>


Perfect, with one correction COUNTA

Thanks

I was assuming they were all numbers <vbg>

Good spot!

Bob

"Kris" <witek7205@spam.gazeta.pl.invalid> wrote in message
news:do79n4$slh$1@inews.gazeta.pl...
> Bob Phillips wrote:
> > Sort A1:A7 so that the empties are at the bottom and use
> >
> > =OFFSET($A$1,,,COUNT($A$1:$A$7),1)
> >
>
>
> Perfect, with one correction COUNTA
>
> Thanks
>
>