I have had another look at your code and I think that it should look
something like this when re written to incorporate the method I previously
posted. Hope it all helps.
NOTE: I have used column A.

Sub Macro2()

'Note: Do not Dim lastrow4 because it is a name on
'the worksheet not a VBA variable

Dim lastrow2 As Range

ActiveWorkbook.Names.Add Name:="lastrow4", _
RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "A").End(xlUp)

Set lastrow2 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp). _
Offset(1, 0)

lastrow2.FormulaR1C1 = "=SUM(R1C1:lastrow4)"

End Sub

Regards,

OssieMac

"Nigel" wrote:

I am trying to use code using a fixed starting point and a variable end point

the code I am using is this

Dim lastrow4 As Long
lastrow4 = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row * -1
Range("D" & lastrow2) = "=SUM(R[lastrow4]C:R[-1]C)"
Range("E" & lastrow2) = "=SUM(R[lastrow4]C:R[-1]C)"

the idea is that lastrow4 is the lastrow, in code if it was a fixed start
and end point it would read

Dim lastrow2 As Long, lastrow4 As Long
lastrow4 = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row * -1
lastrow2 = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row + 1
Range("D" & lastrow2) = "=SUM(R[-63]C:R[-1]C)"


the first cell in the calculation is always D7 or E7 etc but the ending cell
is variable, any help is as alway appreciated