Solved: Changing Range in comboBox

[ijswalker]

Does anyone know how to limit the number of items in my list to show in my combo box. Right now I have specified the range for my list

e.g A1:A30

When I select from the list, the combo box displays all items from A1:A30 including those rows that are blank. Over time the user will enter more items into the combobox and when it is saved it will populate the range.

However, this range might go beyond 30 items and I would like the combo box to only display the items in the list. If there are 5 items then display 5 items. If there are 100 items then display 100 items.

What I'm really getting at is that I don't want to have to mess about with increasing ranges everytime I add or delete a new item to the list. Can this be done automatically?

Thanks

Ian

[lucas]

If its a combobox on a userform its the ListRows property. If its a combobox on a sheet, right click on it and click format control....then click the control tab and set the number of rows in the drop down lines box.....

[XLGibbs]

You can create a named range which is dynamic and use that named range as your row source...

[vba]
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A1:$A1000),1)
[/vba]

would refer to a range beginning in Sheet1 A1 and in this case, max of 1000 rows. The CountA function counts how many are occupied (i.e. not blank) and if your row source is linked to that named range, it would only contain as many items as are in your list.

Hope that helps.

[ijswalker]

The dynamic range works very well once I figured out that all external sheet ranges need to be named.

Thanks

Ian

[XLGibbs]

I should have told you that..didn't know the ranges were on external files...

Just be sure to mark thread solved and rate it if appropriate!