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

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

Dave wrote:
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

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" <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

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