I am trying to set the InputRange of a combobox on a sheet (not using a
form) via vba. I thought the following code would work, but I get a space
between the last column reference and the rownumber.

Dim i As Integer
Dim s As String
i = 13 + Sheet8.Range("H11").Value 'The number of rows not empty
s = "I13:I" & Str(i)
Worksheets("Estimate").Shapes("cboMultSize").Contr olFormat.ListFillRange = s


For example, if i = 2, then s turns out to be "I13:I 15", I need this to be
"I13:I15". Can anyone help me here?

Thanks.

The Str function always output a leading space for positive numbers (it is
an ancient function from the early days of BASIC and the space is sort of a
place holder in case the number were negative). You could apply the Trim
function to it...

s = "I13:I" & Trim(Str(i))

or use the Mid function to skip over it...

s = "I13:I" & Mid(Str(i), 2)

but the easiest thing to do is not use that function at all; use the CStr
function instead...

s = "I13:I" & CStr(i)

Rick


"Nathan Guill" wrote in message
...
I am trying to set the InputRange of a combobox on a sheet (not using a
form) via vba. I thought the following code would work, but I get a space
between the last column reference and the rownumber.

Dim i As Integer
Dim s As String
i = 13 + Sheet8.Range("H11").Value 'The number of rows not empty
s = "I13:I" & Str(i)
Worksheets("Estimate").Shapes("cboMultSize").Contr olFormat.ListFillRange =
s


For example, if i = 2, then s turns out to be "I13:I 15", I need this to
be "I13:I15". Can anyone help me here?

Thanks.

Try

s = "I13:I" & Trim(Str(i))

Hope this helps,

Hutch

"Nathan Guill" wrote:

I am trying to set the InputRange of a combobox on a sheet (not using a
form) via vba. I thought the following code would work, but I get a space
between the last column reference and the rownumber.

Dim i As Integer
Dim s As String
i = 13 + Sheet8.Range("H11").Value 'The number of rows not empty
s = "I13:I" & Str(i)
Worksheets("Estimate").Shapes("cboMultSize").Contr olFormat.ListFillRange = s


For example, if i = 2, then s turns out to be "I13:I 15", I need this to be
"I13:I15". Can anyone help me here?

Thanks.

What happens if you just use:

s = "I13:I" & i



Nathan Guill wrote:

I am trying to set the InputRange of a combobox on a sheet (not using a
form) via vba. I thought the following code would work, but I get a space
between the last column reference and the rownumber.

Dim i As Integer
Dim s As String
i = 13 + Sheet8.Range("H11").Value 'The number of rows not empty
s = "I13:I" & Str(i)
Worksheets("Estimate").Shapes("cboMultSize").Contr olFormat.ListFillRange = s

For example, if i = 2, then s turns out to be "I13:I 15", I need this to be
"I13:I15". Can anyone help me here?

Thanks.

--

Dave Peterson

Thanks Rick, the CStr worked perfectly.

"Rick Rothstein (MVP - VB)" wrote in
message ...
The Str function always output a leading space for positive numbers (it is
an ancient function from the early days of BASIC and the space is sort of
a place holder in case the number were negative). You could apply the Trim
function to it...

s = "I13:I" & Trim(Str(i))

or use the Mid function to skip over it...

s = "I13:I" & Mid(Str(i), 2)

but the easiest thing to do is not use that function at all; use the CStr
function instead...

s = "I13:I" & CStr(i)

Rick


"Nathan Guill" wrote in message
...
I am trying to set the InputRange of a combobox on a sheet (not using a
form) via vba. I thought the following code would work, but I get a space
between the last column reference and the rownumber.

Dim i As Integer
Dim s As String
i = 13 + Sheet8.Range("H11").Value 'The number of rows not empty
s = "I13:I" & Str(i)
Worksheets("Estimate").Shapes("cboMultSize").Contr olFormat.ListFillRange
= s


For example, if i = 2, then s turns out to be "I13:I 15", I need this to
be "I13:I15". Can anyone help me here?

Thanks.