last row of sheet1 to use in a formula in sheet2
 

I would like to use the last row number as a value in the last row of a range.

For example, the following formula will determine a row number that I can
display in a message.:
LRow = ThisWorkbook.Sheets("sales").Cells(Rows.Count, 1).End(xlUp).Row

Now, on another worksheet in the same workbook, I have a macro that at some
point has the following line:
Range("A1:E1").Select
Selection.AutoFill Destination:=Range("A1:E201"), Type:=xlFillDefault

I would like the LRow value to be inserted in place of the 201 in the range
above. I tried Range("A1:E(SLRow)") but that didn't work.

This must be easy, but maybe I am tired and so it eludes me.

last row of sheet1 to use in a formula in sheet2
 

Anything included between quote marks in a string value is considered as
text... your SLRow is nothing more than 5 characters of text where you put
it. In order to get the variables *value* inserted at the location you
indicated, you have to concatenate it there. Try this...

Range("A1:E(" & SLRow & ")")

Now VB will see SLRow as the variable and will substitute its value at that
location.

--
Rick (MVP - Excel)


"Solutions Manager" wrote in
message ...
I would like to use the last row number as a value in the last row of a
range.

For example, the following formula will determine a row number that I can
display in a message.:
LRow = ThisWorkbook.Sheets("sales").Cells(Rows.Count, 1).End(xlUp).Row

Now, on another worksheet in the same workbook, I have a macro that at
some
point has the following line:
Range("A1:E1").Select
Selection.AutoFill Destination:=Range("A1:E201"), Type:=xlFillDefault

I would like the LRow value to be inserted in place of the 201 in the
range
above. I tried Range("A1:E(SLRow)") but that didn't work.

This must be easy, but maybe I am tired and so it eludes me.

last row of sheet1 to use in a formula in sheet2
 

Just a typo that was in the original that was carried over in your response:

Range("A1:E(" & SLRow & ")")
should be:
Range("A1:E" & SLRow)
or
Range("A1:E" & LRow)

I'm know that the ()'s shouldn't be there. I'm confused by the SLRow/LRow
stuff, though.

Rick Rothstein wrote:

Anything included between quote marks in a string value is considered as
text... your SLRow is nothing more than 5 characters of text where you put
it. In order to get the variables *value* inserted at the location you
indicated, you have to concatenate it there. Try this...

Range("A1:E(" & SLRow & ")")

Now VB will see SLRow as the variable and will substitute its value at that
location.

--
Rick (MVP - Excel)

"Solutions Manager" wrote in
message ...
I would like to use the last row number as a value in the last row of a
range.

For example, the following formula will determine a row number that I can
display in a message.:
LRow = ThisWorkbook.Sheets("sales").Cells(Rows.Count, 1).End(xlUp).Row

Now, on another worksheet in the same workbook, I have a macro that at
some
point has the following line:
Range("A1:E1").Select
Selection.AutoFill Destination:=Range("A1:E201"), Type:=xlFillDefault

I would like the LRow value to be inserted in place of the 201 in the
range
above. I tried Range("A1:E(SLRow)") but that didn't work.

This must be easy, but maybe I am tired and so it eludes me.

--

Dave Peterson

last row of sheet1 to use in a formula in sheet2
 

You were right and the answer is perfect. As for the other, this is me not
typing well. SLRow and LRow should have been the same. Thank you for your
help and patience. This forum is an oasis in a desert.

"Dave Peterson" wrote:

Just a typo that was in the original that was carried over in your response:

Range("A1:E(" & SLRow & ")")
should be:
Range("A1:E" & SLRow)
or
Range("A1:E" & LRow)

I'm know that the ()'s shouldn't be there. I'm confused by the SLRow/LRow
stuff, though.