I will put that in the code. It looks like it just checks to make
sure the range is solidly filled?
It is great for error checking. THANKS!!
On Jan 22, 4:13 pm, JLGWhiz wrote:
Try this

Sub UserForm_Initialize()
Dim lngLastRow As Long
Dim ws As Worksheet
Dim c As Range
Dim rng As Range
Set ws = Worksheets("patients")
lngLastRow = Cells.Find(What:="*", After:=ws.Range("C1"), _
SearchDirection:=xlPrevious).Row
'lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row
Set rng = ws.Range("C1:C" & lngLastRow)
For Each c In rng
If Not c Is Nothing Then
ws.ComboBox2.AddItem c.Value
End If
Next c
ws.ComboBox2.AddItem "All"
ws.ComboBox2.AddItem "Exit"
End Sub

"Janis R" wrote:
I'm getting some strange results from the macro. If I use the first
variable lngLastRow I get only 22 rows in my combo box. If I use the
second varIABLE lngLastRow then I get 6. The strange thing is I have
30 rows in A,B and C in my sheet. So can you tell me what is wrong
with my combox script? All I really want is row C in the combox list

tia,
patients.xls:
A B C
1 fName Lname Fname &" "&Lname
,,,,
30

-------
Sub UserForm_Initialize()

Dim lngLastRow As Long
Dim ws As Worksheet
Dim c As Range
Dim rng As Range

Set ws = ThisWorkbook.Worksheets("patients")
lngLastRow = Cells.Find(What:="*", After:=ws.Range("C1"), _
SearchDirection:=xlPrevious).Row

'lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row

Set rng = ws.Range("C1:C" & lngLastRow)

For Each c In rng.Cells
Me. ComboBox2.AddItem c.Value
Next c

Me.ComboBox2.AddItem "All"
Me.ComboBox2.AddItem "Exit"

End Sub

that might not