setting a list in a combox doens't from a range

[Janis R]

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

 

[Janis R]

I found the answer to this problem if anyone cares
What happened is my code was correct but the user had locked some
cells therefore it counted correctly up to those cells. thanks,

 

[JLGWhiz]

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

 

[Joel]

The following statment is only checking column "C"

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

Rows.Count = 65536, so this statment says to got to cells(65536,"c") and
search upo until data is found

This stement is using cells to indicat every cell in the worksheet
lngLastRow = Cells.Find(What:="*", After:=ws.Range("C1"), _
SearchDirection:=xlPrevious).Row


To change this to column C do the following
lngLastRow = Columns("C:C").Find(What:="*", After:=ws.Range("C1"), _
SearchDirection:=xlPrevious).Row

 

[XL Programmer]

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

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

that might not

 

[XL Programmer]

I was also really confused about the row counts. Thanks for taking
the time to explain the difference.

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




that might not

I

 

[XL Programmer]

It now makes sense, I am looking in a column for rows not in the sheet
for cells. thanks. It could have been part of the memory prob.