Totaling Rows up to Row N

Let's say that you're in row 2 (allowing for headings in row 1).
The formula in H2 would be sum(b2:g2)
You can copy and paste that formula, but it's easier to "drag" it, or autofill it.
Click on H2, and you'll see a small dot in the bottom right corner of that cell. Point to the dot, then drag downwards. The formula is copied, and it always gives you the total for that row.

If there isn't any other data in the H column below row N, you can do it like this:

=SUM(H:H)      <-- sums all numbers in the entire H column

But if you have a specific range you need to sum (e.g. sum of H1:HN) where "N" is some number, really you want to be able to construct the named range string dynamically.

E.g. Today you want the cell's value to be:

=SUM(H1:H55)

But next week, when there's more rows, you want it to be

=SUM(H1:H234)

The simplest way to accomplish this is just to manually edit the cell which is holding this formula.

But if you're trying to make it more automatic than that, you can use the INDIRECT() and CONCATENATE() functions to dynamically assemble a string that contains the named range you want to sum.

Example:

     * You want to sum all of the cells from H1 through H234
     * You have stored the value 234 in the cell "J1"

You can use the following formula to accomplish that:

  =SUM(INDIRECT(CONCATENATE("H1",":","H",J1)))

The CONCATENATE() function will assemble the parts into a single string, e.g. "H3", ":", "H", and the value stored in the J1 cell are concatenated together, resulting in the string "H1:H234".

The INDIRECT() function takes that string and parses it into a named range

The SUM() function sums that named range.

Here is a try: You may change column L by the lowest column in your sheet.

Have a macro to do the job. Can be assigned to a command button.
Here is an example in a worksheet.
      L          M          N
1    vl      vm   Total
2    1      2      3
3    2      3      5
4    2      3      5
5            

Private Sub UpdateN_Click()
    Dim ws1 As Worksheet '
    Dim aws1 As Worksheet ' active worksheet
    
    Dim rno As Long 'row number
    'Dim cno As Long ' column number
    Dim rno1 As Long 'row number 1
    Dim cno1 As Long ' column number 1
    Dim rno2 As Long 'row number 2
    Dim cno2 As Long ' column number 2
    Dim cnt As Long 'count
    Dim rng1 As Range
    '
    Set wb1 = Application.ActiveWorkbook
    Set ws1 = Application.ActiveSheet
    ws1.Activate
    Set aws1 = wb1.ActiveSheet
 
    'find start and end of rows in data
    rno1 = aws1.Range("M1").End(xlUp).Row
    rno2 = aws1.Range("M1").End(xlDown).Row
    ' assign column number for N column
    cno1 = Cells(rno1, "N").Column
    ' copy N2 formula to new N cells
    Dim N2Formula As String
    Dim N2Range As Range
    M2Formula = Cells(rno1 + 1, cno1).Formula
    Set N2Range = aws1.Range(Cells(rno1 + 1, cno1), Cells(rno2, cno1))
    N2Range.Formula = "=Sum(L" & rno1 + 1 & ":M" & rno1 + 1 & ")"
    Cells(rno2 + 1, "N").Formula = "=Sum(N" & rno1 + 1 & ":N" & rno2 & ")"
    
End Sub

Select allOpen in new window

Result:
      L          M          N
1    vl      vm   Total
2    1      2      3
3    2      3      5
4    2      3      5
5            13

New row added:
Result:
      L          M          N
1    vl      vm   Total
2    1      2      3
3    2      3      5
4    2      3      5
5    9         9              13

Result:
      L          M          N
1    vl      vm   Total
2    1      2      3
3    2      3      5
4    2      3      5
5    9         9              18
6                                31

I'm sorry..... I meant to have a solution in VBA....not in a macro or any other method.

I need the solution in VBA please!

Thank you.