Are you sure about no gaps/empty cells from E2 all the way to the end of your
list? That's the only way I can duplicate the limitation using Excel 2003.

But just in case, try this code instead of the one posted earlier (and if
you're using Excel 2007, change Rows.Count to Rows.CountLarge) along with
whatever other changes you may have had to make for your specific
workbook/sheet. The previous code would give you the address of the last
cell below E2 with something in it - an empty cell would cause it to stop.
This code will find the last cell in column E with something in it, so in
effect it ignores gaps in your list. Of course, if you've got stuff in
column E below the bottom of your list, it's going to include those into the
list also. The limit for # of items is far greater than 24 or 36. The limit
of items (in Excel 2003) in a Data Validation list is 1024, and it's far
greater than than for a combo or listbox from one of the toolbars.
Private Sub ComboBox1_GotFocus()
Dim listRange As String

'if gaps in the list
listRange = "Sheet1!E2:" &
Worksheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Address
ComboBox1.ListFillRange = listRange

End Sub


" wrote:

Dear JLatham,

NEW QUESTION:

The codes work fine, however when I actually tested it out, the display on
the Combo Box shows 26 rows of data. My data (listing) is 34 rows (no empty
lines in between).

What I've Done
1) I have checked the Dynamic Range, the range is 34 rows.
2) Checked the Combo Properties for data limitation (can't find one)

Please advice. Thank you.