I trid your loop, however as I posted in the begining I'm using an activex
Combo box not a userform. Also it turns out an error "permission denied".

"Dave Peterson" wrote:

=counta() will return the number of cells that aren't empty. If a cell contains
a formula, then it's not empty--no matter if it evaluates to "" or anything
else.

Maybe you could loop through the range and populate the combobox with the values
using .additem:

Option Explicit
Private Sub UserForm_Initialize()

Dim sh As Worksheet
dim myRng as range
dim myCell as range

Set sh = ActiveSheet

With sh
set myrng = .range("I2",.cells(.Rows.Count, "i").End(xlUp))
End With

for each mycell in myrng.cells
if mycell.value = "" then
'skip it
else
me.combobox1.additem mycell.value
end if
next mycell

End Sub

(Untested, uncompiled.)

art wrote:

First of all I can't use it because there is a problem with this:

"The ListFillRange property of an ActiveX control does not update properly
when rows or columns in the ListFillRange are inserted or deleted"
(http://support.microsoft.com/kb/160206)

Besides, I don't know why, but it does not work, The combobox list is
nothing (empty).

I am trying to use WorksheetFunction.CountA(Range("I:I")) but I don't know
why is the result 2000 (which 2000 has formulas in them) while only 10 are
not empty (only ten cells are filled with info). Please help.

Thanks

"Dave Peterson" wrote:

Maybe you could drop the code altogether and use a dynamic range name.

Debra Dalgleish explains dynamic range names he
http://contextures.com/xlNames01.html#Dynamic

art wrote:

I think I got it strieght a little I changed it to listfillrange. However the
problem is that the column I has a formula, so It goes down. And most
importantly, The combo list is long but I don't see and thing in the list. It
is empty. Here is the formula that I adjusted:

Dim sh As Worksheet
Dim lstRw As Long
Dim srcRng As String
Sheets("Customer List").Select
Set sh = ActiveSheet
With sh
lstRw = .Cells(.Rows.Count, 9).End(xlUp).Row
End With
srcRng = Range("I2:I" & lstRw).Address(external:=True)
ComboBox1.ListFillRange = srcRng


"Dave Peterson" wrote:

There were a couple of typos in the original code:

Option Explicit
Private Sub UserForm_Initialize()

Dim sh As Worksheet
Dim lstRw As Long
Dim srcRng As String

Set sh = ActiveSheet
With sh
lstRw = .Cells(.Rows.Count, 9).End(xlUp).Row
End With
srcRng = Range("I2:I" & lstRw).Address(external:=True)
Me.ComboBox1.RowSource = srcRng

End Sub





art wrote:

It's a active x combo box embdded in the sheet, however I tried it and syntax
error comes up, with srcRng = Range("I2:I & lstRw).Address highlighted?

"JLGWhiz" wrote:

This would be incorporated into the UserForm initialize
event code.

Dim sh As Worksheet, lstRw As Long
sh = ActiveSheet
lstRw = sh.Cells(Rows.Coount, 9).End(xlUp).Row
srcRng = Range("I2:I & lstRw).Address
Me.ComboBox1.ListFillRange = srcRng

"art" wrote:

Hello:

How can I set a combo list to a range in a certain sheet that the length of
the range keeps changing and new names are added to the list?

For e.g. the list starts off from I2:I10, however I keep adding to the list
and I want the combobox to add the newer ones to the list as well. I started
using ComboBox1.ListFillRange = MyRange, which is I2:last cell in this column?

Any help?

Thanks.

Art

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson