Create Listbox to display data stopping at last row of range

Can anybody guide me, how can i make my list box display data in the range, stopping at last row. For example, In the list box control i have set the area from B10 to B50, but initially i have data only in B10 to B20 and 30 rows are blank. This will be used in future and accordingly only the rows with data should be listed. Thanx.

Hi Kprajath,
what type of listbox and where? activex or userform ,worksheet or userform?

Hi Pike
thanks for responding. It was for listbox - fom control as well as for worksheet drop down list. i have solved it by modifying my reference formula in the format control as "=OFFSET(Sheetname!$B$4,0,0,COUNTA(Sheetname!$B:$B)-1). Now it displays only items in the range, but expands whenever i add a new item.

However, you may help me in another problem. When i change a cell (say cell A1 in the worksheet where value of selection is linked) by selecting a name from the listbox, all cells linked with Vlookup formula for various info are promptly updated. i need a solution for auto triggering a VBA function in the same sheet to update some other data (few rows below) related to the selected item as this requires some sorting, row count etc. and can't be directly linked by Vlookup. I can do it by assigning a command button to the function and ask the user to click it, but i wish to do it automatically when the new item is selected from the listbox. i have tried worksheet change event method, but it is not acting when the change is happening by way of selection from list box. (If i type something manually in the cell (A1) the event handling procedure works.) If you have a better idea, Please enlighten me. Thanks.

While solving the problem with list box, i realised this "=offset...." method can work in drop down list & form control list box only, but when used with Combo-List box with Active X control, it will not display newly added items. Do anybody have a solution to this ?