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