Thanks to you both for the replies.
used Bob's and it worked like a charm

dave
"Dave" <dave@accessdatapros wrote in message
...
I am using the following code to populate a drop down on a FORM in Excel.

Public Sub PopulatePositions()
Dim AllCells As Range, Cell As Range
Dim item As Variant
Set AllCells = Worksheets("Positions").Range("A2:A150")
On Error Resume Next
For Each item In AllCells
Successor.cboPositionID1.AddItem item
Next item

As you can see I have set the range to be from A2 to A150 which assumes
148 records.

There is not currently 148 records (104) so I end up will a lot of empty
rows on the dropdown.
The number of records will be changing so it would be nice to get a
current count of rows on the sheet when the FORM "Initializes" and then
only populates that many rows on the FORM Drop down.

Any help here will be appreciated.

Thanks in advance
dave