# Solved: Excel Combo box Rowsource problem



## engti (Oct 5, 2005)

Hi,

I'm trying to write a VBA procedure which picks up values from a spreadsheet and displays them in a drop down combo box.

There is a list of items in Column A. I am using

v = Application.CountA("A:A")

to tell me how many items there are in Column A.

I know that you can use

Me.sName1.RowSource = "A1:A8"

but is there a way I can display the contents of A1:A_r_ in the combobox sName1
something like

Me.sName1.RowSource = "A1:A_r_"

Obviously this doesn't work, but I dont want to hardcode the no. of items as they keep on getting added.

Thanks a lot,

Robin


----------



## bomb #21 (Jul 1, 2005)

If column A is just for the list of items and there are no blanks, you could use a formula to define a dynamic named range.

1. Hit *CTRL+F3*

2. Name = *Items* (for example)

3. Refers to: *=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)*


----------



## engti (Oct 5, 2005)

Cool...got it....

vName1.RowSource = "A2:A" & v 

does the trick..... it loads up all the cells which are not empty and sequential in column A to the combo box named vName1


----------

