Populate List Box in Excel with visible rows when worksheet gets filtered

[adamsm]

Hi,

In my workbook; the column “B” of rows 5 and 6 contain the serial Number “0697”.

Row 7 of the same column has the text “0698”. And again the same column of row 8 has the serial Number “0697”.

Supposing I want to filter all the rows that have “0697” from my user form list box I get only the rows 5 and 6 filtered in the list box even though the worksheet shows all the three rows where it contains the text “0697”.

How could I change the code so that it populates the list box with all the three rows where it contains the text “0697”.


I've attached the workbook for your reference.

Any help on this would be kindly appreciated.

[p45cal]

see if the attached does anything for you.

Your code only added the first area of the xlVisiblecells range.

[adamsm]

Thanks p45cal It does do what I have asked.

[adamsm]

If I may ask, how could I make the column headers visible in the list box. It does not make visible by enabling form the properties window.

[Bob Phillips]

You have to bind the data to the listbox to get headings.

[adamsm]

Like how?!

[Bob Phillips]

You have to set the RowSource property of the ListBox, but you don't want a contiguous dataset, you want the filtered results, and you cannot bind to that unless you copy it off somewhere else.

[adamsm]

Thanks for the reply Xld. I'll try to figure that out.

[p45cal]

For a non-contiguous range, you'll have difficulty using rowSource, even more difficult to use ColumnHeaders. In the past I have used a hidden sheet to prepare a range to use as a RowSource for a list box with ColumnHeaders
There is such a hidden sheet called DataList in the attached.

Like how?!

[Bob Phillips]

For a non-contiguous range, you'll have difficulty using rowSource, even more difficult to use ColumnHeaders.

Read ... can't!

[adamsm]

Thanks for the Help p45cal. I could view the column headers now.