list box-hide empty rows

**sunilmulay** · 06-19-2009, 10:19 AM

Is it possible to have a list box only list rows that contain data in them?
For example if you have a list box whose input field is A1:A10. If rows A2:A9 are empty, I would like the first (only) two rows in the list box to be A1 and A10.

I am using a Form control not an Active X control list box, because the Active x one was giving me other problems.

I tried inserting a macro that actually hides the empty rows (A2:A9 in this example) on the actual sheet, but that doesn't make any difference to what appears in the list box - you still see all the empty rows there.

Thanks
Sunil

**MickG** · 06-19-2009, 11:16 AM

Try This:-

Please Login or Register  to view this content.

Regards Mick

**sunilmulay** · 06-19-2009, 01:25 PM

Hi Mick

Thanks for your suggestion.

My example of rows A1 and A10 being the ones with data is just one possible scenario. This is actually driven by the user. I may be that rows A1, A2 and A10 have data with the rest in the middle being blank.

Can you suggest an alternative code which will delist the blank rows whatever they may be?

Thanks
Sunil

**MickG** · 06-20-2009, 10:05 AM

Hi, This will fill your ListBox with all cell values in column "A" that are not Blank.

Please Login or Register  to view this content.

Regards Mick

**delolding** · 08-29-2012, 11:25 AM

Hi tried for Excel 2003 but says
run time error 438
object doesnt support this propoerty or method

**arlu1201** · 08-29-2012, 11:30 AM

Delolding,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.