Sum x number of rows

[sm789]

Hi I need to insert a code in a macro which needs to Sum x number of rows just above it. X is a variable which is stored in SortCount earlier in the macro (its the number of instances hence the number of rows). The range is also named as MyRange. I had earlier put

ActiveCell.FormulaR1C1 = "=sum(MyRange)"

which works but since this macro needs to run several times without disturbing previously stored data, I need to delete all names which then results an error for the above formula.

So either please suggest how to convert the above so that the formula has the actual cell range instead of the name or simply advise a code that will do something like:

ActiveCell.FormulaR1C1 = "=sum(R[-SortCount]c[0]:R[-1]C[0])"

Thank you for your help.

[mikerickson]

Perhaps
[VBA]With ActiveCell
.Value = WorksheetFunction.Sum(Range(.Offset(-SortCount,0), .Offset(-1, 0))
' or
.FormulaR1C1 = "=SUM(" & Range("MyRange").Address(True, True, xlR1C1, True) & ")"
End With[/VBA]

[sm789]

Thank you. the second option did exactly what I wanted. I am fairly new to coding so I have a "stupid" question: which is a better way of writing code:

With ActiveCell
.FormulaR1C1 = "=SUM(" & Range("MyRange").Address(True, True, xlR1C1, True) & ")"
End With

OR
ActiveCell.FormulaR1C1 = "=SUM(" & Range("MyRange").Address(True, True, xlR1C1, True) & ")"

Thank you for your help.

[mikerickson]

The second (one line format) is cleaner and easier to read.

The main reason for the With...End With construction was for the line that you rejected.