Steve,

That is exactly what I wanted, thank you. I am not sure what the "Len(cel)"
is but HELP should help me out.

Thanks again.

Cody

"STEVE BELL" wrote:

Cody,

My bad... from your code I thought that is what you wanted.

But to just review the cells and add to the combobox without assigning
ranges....

Dim cel As Range

Sheets("sheet1").ComboBox1.Clear

For Each cel In Sheets("Sheet1").Range("B4:B100")
If Len(cel) 0 Then
Sheets("sheet1").ComboBox1.AddItem cel.Text
End If
Next

--
steveB

Remove "AYN" from email to respond
"Cody" wrote in message
...
Steve,

The main problem I am trying to get away from is assigning the values to
another column to enter in the ListFillRange property. I would like to
figure out how to decide which values need to be applied to ListFillRange
and
do so with a program rather than filling a reference range of cells.

"STEVE BELL" wrote:

Cody,

Try the code below.

If column B has blank cells below a certain point, than copying the
entire
range over to
column X should work. And the countif should give you the list range.

If column B has some entries, than some blanks, and than some entries -
you
will have to use a loop.

But I think this will work (?).... (code untested)

====================
Sub ModelListFill()

Dim ModelNumber As Integer
Dim i As Integer
Dim cel As Range
Dim rng As Range

Sheets("Defaults").Range("X4:X100").Value =
Sheets("Defaults").Range("B4:B100").Value
ModelNumber =
WorksheetFunction.CountA(Sheets("Defaults").Range( "B4:B100"))
+ 3

Set rng = Sheets("Defaults").Range("X4:X" & ModelNumber)

Sheet1.ComboBox6.ListFillRange = rng

End Sub
==================================

--
steveB

Remove "AYN" from email to respond
"Cody" wrote in message
...
I am using the following code to fill the list in a combobox. As you can
see
the values are assigned to cells. The cell reference to B54 is the line
after
the code. EXCEL gets extremely slow when assigning the values. I am
wondering
if it will speed up tremendously if I code the entire operation rather
than
using the cell values. If so how do I store each additional value that
will
be added to the list programmatically. The output should be fairly
straightforward.


Sub ModelListFill()

Dim ModelNumber As Integer
Dim i As Integer
ModelNumber = 0
For i = 4 To 100
If Sheets("Defaults").Range("B" & i) < "" Then
Sheets("Defaults").Range("X" & i).Value = Sheets("Defaults").Range("B"
&
i).Value
ModelNumber = ModelNumber + 1
Else
i = 100
End If
Next

Sheets("Variables").Range("B54").Value = ModelNumber
Sheet1.ComboBox6.ListFillRange = Sheets("Variables").Range("B55").Value

End Sub


="Defaults!X4:X"&B54+3


Thanks for any help