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

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

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

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,

On Jan 21, 12:15 pm, 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

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

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

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

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

"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

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

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

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

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

On Jan 22, 5:32 pm, XL Programmer wrote:
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



I

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

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.

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