just tried this myself and it worked

lstclid.columncount = 4
lstCLID.RowSource = "c2:f" & strendrange


make sure to set you column count or not all the data will display
--
When you lose your mind, you free your life.


"ben" wrote:

UserForm1.lstCLID.RowSource = ThisWorkbook.Sheets(3).Range("C2:F" & _
strEndRange)
maybe deleting that last quote? but i can't tell where it came from?


and what error do you recieve, is it a type mismatch or is it a problem
loading the value into the rowsource of the control?


When you lose your mind, you free your life.


"Henry" wrote:

Here's code I have placed in a command button on an Excel 2003
spreadsheet. I'm getting the last non-empty row on a sheet so I can use
it to set a range. I am so far unable, however, to determine how to
concatenate this numeric value as part of the range expression below.
I'd be very grateful if someone could oint out how to do this. Thanks!


Henry
DPM Mellon

----------------------------------------------------------------------

Private Sub cmdShow_Form_Click()

Dim intLastRow As Integer
Dim strEndRange As String

ThisWorkbook.Sheets(3).Activate
intLastRow = ActiveSheet.Range("A65536").End(xlUp).Row

' ThisWorkbook.DeleteEmptyRows Range("C2:F65536")
strEndRange = Trim(Str(intLastRow))

' -----------------------------
' This is the problematic line
' -----------------------------
UserForm1.lstCLID.RowSource = ThisWorkbook.Sheets(3).Range("C2:F" & _
strEndRange)"

UserForm1.Show

End Sub