Dynamically Populate Listbox - Exclude Empty cells

Question

I am creating a form in excel (not a userform) and I am populating the listbox using cells. However, these cells are sometimes A1:10 and sometimes they are A1:A4. Is there a way to dynamically change what is shown in the listbox?

Right now, when I use A1:10 and there are only 4 cells populated, I get the list of 4 populated cells followed by 6 blank entries. I'd like to get rid of the 6 blanks when there are only 4.

Answer 1

I've never tried using dynamic ranges with list boxes, but it might work.

Take a look at this link. I use this often for data validation. http://www.ozgrid.com/Excel/DynamicRanges.htm

If that doesn't work, code will be required. Let me know.