VBA to sum formula up to the last row

[atomazos]

I have a defined named range and i want to add at the last row of the defined named range a sum formula which will sum the rows above. Can anyone help please?

 

[dnlrsms]

Hi atomazos,

Try this. I assumed that your defined name range will remain in the same column, just increase in values that should be calculated. If this assumption is not correct, please provide more details.

Please change the "8" in the lastrow (lastrow = .... (Rows.Count, 8)..... to the column the defined range is in.

Sub myNamerng()

Dim rng As Range
Dim lastrow As Long


lastrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 8).End(xlUp).Row 'Look for the last row in the Defined named range


Set rng = ThisWorkbook.Sheets("Sheet1").Range("H2:H" & lastrow) 'The range of the Defined named range


With rng
  On Error Resume Next
    ActiveWorkbook.Names("Name1").Delete 'Delete the current Defined name range; Change the 'Name1" tag to your current named range
        ActiveWorkbook.Names.Add Name:="Name1", RefersTo:=rng 'Assign a new Defined name range based on rng range
          .Cells(lastrow).Formula = "=SUM(Name1)" 'Insert the SUM formula
End With


End Sub

 

[atomazos]

Hi dnlrsms,

Thank you for your reply!

The issue here is that i have multiple defined name ranges in my file. And i want to have automated sum formula which will sum up the above rows. I don't want to define a fixed column in my code and this is because each time i will add a row or a column i should adjust my code as well.

Thanks,

A.