Hi JN,

I am trying to set up a macro to fill up formulas two rows below the last
row of a worksheet. The problem is this number of rows is different each
week. I have a hard time making this macro works for different week.


To return the last data row in a worksheet, you could use the following
function posted by Ron de Bruin:

'=================
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
'<<=================

In your code you could use it like:

Dim Lrow as long

Lrow = LastRow(ActiveSheet)


---
Regards,
Norman



"JN" wrote in message
...
Hi,

I am trying to set up a macro to fill up formulas two rows below the last
row of a worksheet. The problem is this number of rows is different each
week. I have a hard time making this macro works for different week.
Thanks.

Below is the code:

Sub Macro13()
'
ActiveCell.FormulaR1C1 = "Total"
Range("M204").Select
ActiveWindow.SmallScoll Down:=12
Range("M199").Select
Selection.Copy
Range("M204").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[1])-SUM(RC[2])"
Range("M205").Select
ActiveWindow.SmallScroll ToRight:=1
Range("N204").Select
ActiveCell.FormulaR1C1 = "=+R[-2]C/R[-203]C"
Range("N204").Select
Selection.AutoFill Destination:=Range("N204:S204"), Type:=xlFillDefault
Range("N204:S204").Select
Selection.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.Font.Bold = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("M204").Select
End Sub