populating list

[Guest]

How do I find out how many entries I have in a column, when the column length
is changing all the time? I would like to populate a combobox with entries
in a column, but I am constantly adding to the column.

wazooli

 

[Dave Peterson]

You could use a dynamic range. Debra Dalgleish has some notes at:
http://www.contextures.com/xlNames01.html#Dynamic

Or maybe determine the range when you need it:

dim myRng as range
with worksheets("Sheet1")
set myrng = .range("a1", .cells(.rows.count,"A").end(xlup))
end with

 

[Guest]

What I use is a simple excel function to count the populated cells. Use
something like:
=100-COUNTBLANK(A1:A101)
adjust that to whatever cell range you are using and then reference that
cell in the for loop when populating the combobox.

 

[Guest]

Thanks Dave - I'll try that. Is this going to get screwed up if some of the
cells are merged? I have merged rows separating years..."1999", "2000", etc.

 

[Dave Peterson]

The only way I know to check to see if merged cells screw things up is to test
it.

What happened when you tried it?