software

range(quot;d1quot;).Validation.add formula1:=
quot;=$A$1:$A$7quot;,Type:=xlValidateList,operator:=xlBetw eenHow 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)

quot;Krisquot; gt; wrote in message
...
gt; range(quot;d1quot;).Validation.add formula1:=
gt; quot;=$A$1:$A$7quot;,Type:=xlValidateList,operator:=xlBetw een
gt;
gt;
gt; How to avoid empty entries in drop down box if some of cell from A1:A7
gt; are empty?
gt;
gt; Thanks
Bob Phillips wrote:
gt; Sort A1:A7 so that the empties are at the bottom and use
gt;
gt; =OFFSET($A$1,,,COUNT($A$1:$A$7),1)
gt;Perfect, with one correction COUNTA

Thanks
I was assuming they were all numbers lt;vbggt;

Good spot!

Bob

quot;Krisquot; gt; wrote in message
...
gt; Bob Phillips wrote:
gt; gt; Sort A1:A7 so that the empties are at the bottom and use
gt; gt;
gt; gt; =OFFSET($A$1,,,COUNT($A$1:$A$7),1)
gt; gt;
gt;
gt;
gt; Perfect, with one correction COUNTA
gt;
gt; Thanks
gt;
gt;