problem setting the used range for a column
 

Can you help me out. The "set range" line doesn't compile. I just need the
used range in column A. It needs to be relative because the user can change
it.
thanks
Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Worksheet
Dim c As Range
Dim rng As Range

ws = xl.Worksheets("patients").Activate()
intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row



SEt rng = Worksheets("patients").Range("A1":A&intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub

problem setting the used range for a column
 

Janis,

Try this

Set rng = Worksheets("patients").Range("A1:A" & intLastRow)

Mike

"Janis" wrote:

Can you help me out. The "set range" line doesn't compile. I just need the
used range in column A. It needs to be relative because the user can change
it.
thanks
Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Worksheet
Dim c As Range
Dim rng As Range

ws = xl.Worksheets("patients").Activate()
intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row



SEt rng = Worksheets("patients").Range("A1":A&intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub

problem setting the used range for a column
 

Thanks very much for your speedy reply.
"Mike H" wrote:

Janis,

Try this

Set rng = Worksheets("patients").Range("A1:A" & intLastRow)

Mike

"Janis" wrote:

Can you help me out. The "set range" line doesn't compile. I just need the
used range in column A. It needs to be relative because the user can change
it.
thanks
Private Sub UserForm_Initialize()

Dim intLastRow As Integer
Dim ws As Worksheet
Dim c As Range
Dim rng As Range

ws = xl.Worksheets("patients").Activate()
intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row



SEt rng = Worksheets("patients").Range("A1":A&intLastRow)
ComboBox2.Clear
For Each c In Worksheets("patients").rng
ComboBox2.AddItem c.Value
Next c

End Sub