View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Solutions Manager Solutions Manager is offline
external usenet poster
 
Posts: 27
Default 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.