Hi,
I have a source range containing a mix of filled and empty cells with name
"ListSource"

A1: 111
A2: 222
A3:
A4: 444
A5: 555
A6:
A7: 777
A8:
A9:
A10:

In the drop down verification on another sheet I refere to "=ListSource" and
there it is - all cells including the empty ones. I want the list to show:

111
222
444
555
777

The ListSource has to be in a row, therefore can I not create the list just
with a Autofilter macro...

I have also tried the 'nonVBA way' with John Walkenbach's forumla "Returning
Nonblank Cells from a Range" without been able to reconstruct his result
(oh, yes - i used ctr-shift-enter to make arrays).
{=IF(ISERR(SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))))}

For the very interested i translated it to swedish
{=OM(ÄRFEL(MINSTA(OM(YourData<>"";RAD(INDIREKT("1:"&RADER(YourData))));RAD(INDIREKT("1:"&RADER(YourData)))));"";INDEX(YourData;MINSTA(OM(YourData<>"";RAD(INDIREKT("1:"&RADER(YourData))));RAD(INDIREKT("1:"&RADER(YourData))))))}
..

Maybe I didn't get something right in the interpretation, but hey, it's
better to do it in VBA that I know, rather than trying to make it with this
long formula (even better would be to fully understand the formula, I admit
;-)

Anybody back from summer vacations yet?
/ Regards !