PDA

View Full Version : Populate List Box in Excel with visible rows when worksheet gets filtered



adamsm
06-20-2010, 06:59 AM
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
06-20-2010, 11:39 AM
see if the attached does anything for you.

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

adamsm
06-20-2010, 12:28 PM
Thanks p45cal It does do what I have asked.

adamsm
06-22-2010, 01:27 AM
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
06-22-2010, 01:50 AM
You have to bind the data to the listbox to get headings.

adamsm
06-22-2010, 03:04 AM
Like how?!:banghead:

Bob Phillips
06-22-2010, 03:22 AM
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
06-22-2010, 04:54 AM
Thanks for the reply Xld. I'll try to figure that out.

p45cal
06-22-2010, 05:19 AM
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?!:banghead:

Bob Phillips
06-22-2010, 08:20 AM
For a non-contiguous range, you'll have difficulty using rowSource, even more difficult to use ColumnHeaders.

Read ... can't!

adamsm
06-25-2010, 09:12 AM
Thanks for the Help p45cal. I could view the column headers now.