FORM Drop Down issues

[Dave]

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

 

[JW]

one way:
Dim bRow As Long
bRow=Cells(Rows.Count,"A").End(xlUp).Row
Set AllCells = Worksheets("Positions").Range("A2:A" & bRow)

 

[Bob Phillips]

Public Sub PopulatePositions()
Dim AllCells As Range, Cell As Range
Dim item As Variant
With Worksheets("Positions")
item = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
Me.cboPositionID1.List = item
End With
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave]

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

dave