Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Excel: Concatenation problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Excel: Concatenation problem
Looks like you have an extraneous " (double quote) after the )
Should be: Range("C2:F" & strEndRange) "Henry" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Excel: Concatenation problem
Try
UserForm1.lstCLID.RowSource = ThisWorkbook.Sheets(3).Range("C2:F" & _ intLastRow).Address(,,,True) -- HTH RP (remove nothere from the email address if mailing direct) "Henry" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Excel: Concatenation problem
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Excel: Concatenation problem
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Excel: Concatenation problem
Thanks, folks, for your quick answers. That must have been a record for
me- two answers within a half hour. I've got it straightened out, but in a somewhat different way. I've sent a new message- somewhat related- as a new message thread. Thanks again for your responses, Bob, Ben and Mark. HEnry DPM MEllon Mark H. Shin wrote: Looks like you have an extraneous " (double quote) after the ) Should be: Range("C2:F" & strEndRange) "Henry" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel concatenation | Excel Worksheet Functions | |||
Concatenation in Excel | Excel Worksheet Functions | |||
Concatenation in Excel 2003 | Excel Worksheet Functions | |||
Concatenation Problem / VBA ? | Excel Worksheet Functions | |||
excel save AND concatenation problem | Excel Programming |