Use code to fill the listbox.


ListBox1.rowsource = ""
Listbox1.Clear
for each cell in Range("ListSource")
if cell.Text < "" then
Listbox1.AddItem cell.value
end if
next

--
Regards,
Tom Ogilvy


"Maria J-son" wrote in message
...
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:"&ROW S(Data)))),ROW(INDIRECT("1
:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data<"", ROW(INDIRECT("1:"&ROWS(Dat
a)))),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(I
NDIREKT("1:"&RADER(YourData)))));"";INDEX(YourData ;MINSTA(OM(YourData<"";RA
D(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 !