populating list
 

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

populating list
 

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



Wazooli wrote:

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

populating list
 

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.


"Wazooli" wrote:

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

populating list
 

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" wrote:

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



Wazooli wrote:

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

populating list
 

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?

Wazooli wrote:

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" wrote:

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



Wazooli wrote:

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


--

Dave Peterson