How create a nonblank source for a verification list with a mix of filled & empty cells?

M

Maria J-son

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 !
 
T

Tom Ogilvy

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 said:
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).
Click to expand...
{=IF(ISERR(SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1
:"&ROWS(Data))))) said:
For the very interested i translated it to swedish
Click to expand...
{=OM(ÄRFEL(MINSTA(OM(YourData<>"";RAD(INDIREKT("1:"&RADER(YourData))));RAD(I
 
M

Maria J-son

Thank you Tom, for your rapid answer. As you have helped me before, now you
are here again...

You suggest me to use a Listbox... I stranded on that because I want to have
a uniqe RBG color as backcolor and I can't find out if you can do that.
Therefore, I tried with the verification list in the Excel GUI instead.

Do you know a way of either fill the box with a uniqe RBG color or create a
new range of nonblanks only?

/Kindest Regards
 
T

Tom Ogilvy

Misunderstood that you want to use data validation.

John's formula works fine for me.

It is a multicell array formula, So Define you data as the named range
data. Then select as many cells as there are non empty cells and put in the
formula. Then do Ctrl+Shift+Enter and you should get you list.


Just to make sure you have a good formula, create the defined name Data.
Then select the muliple rows x 1 column range where you want the list
without blanks. then run this code:

Sub MakeFormula()
Dim sStr as String
sStr = "=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))))))"
Selection.FormulaArray = sStr
End Sub

It should put in a working formula array entered.
 
M

Maria J-son

Dear Tom,

You have once again maked it so simple - this time within some minutes you
enclosed a ready-to-use code to ensure the right syntax ! Incredible.

Yes, I hadn't find the right interpretation from english Excel functions to
swedish functions. It was the ISERR that I had choosen the wrong swedish
function.

Thank you once again.
/Regards
 
Top