sum start to end of the row

  • I have made a VBA code that calculates the total for the 3 rows.


    I am unable to rectify the code that calculates total from start to end this is because when the row increases the formula should also be adjusted accordingly.



    Code
    ws1.Range("D" & ws1.Cells(Rows.Count, "B").End(xlUp).Row + 2).Resize(, ws1.Cells(5, Columns.Count).End(xlToLeft).Column - 3).FormulaR1C1 = "=SUM(R[-4]C:R[-2]C)"
    ws1.Range("P7:P" & ws1.Cells(Rows.Count, "B").End(xlUp).Row).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
  • Re: sum start to end of the row


    Please see the sample sheet
    When you click on the command button the total is taken horizontally and vertically and written in their proper column.
    for Vertical total is taken from D7:D9 till the end of the column.
    For horizontal total is taken from D7:O7 for all 3 rows.


    If you now insert a row then the total is taken from D8:D10 where as I require from D7:D10
    Same if you insert a column.


    If you delete the column for formula should also change accordingly.


    The rows and columns changes frequently so the last row and column should be taken into account


    I hope the explanation is clear

  • Re: sum start to end of the row


    try this code


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!