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.
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.
Re: sum start to end of the row
can you attach a sample file with data and desired result ?
Re: sum start to end of the row
will attach in a day as i have to create a sample file
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
Sub Click2()
Dim ws1 As Worksheet
Set ws1 = Worksheets("Sheet1")
LR = ws1.Cells(Rows.Count, "B").End(xlUp).Row
LC = ws1.Cells(5, Cells.Columns.Count).End(xlToLeft).Column
For r = 7 To LR
ws1.Cells(r, LC).Value = Application.WorksheetFunction.Sum(Range(Cells(r, 4), Cells(r, LC - 1)))
Next
For c = 4 To LC
ws1.Cells(LR + 2, c).Value = Application.WorksheetFunction.Sum(Range(Cells(7, c), Cells(LR, c)))
Next
'Columns.AutoFit
End Sub
Display More
Re: sum start to end of the row
Please do not start do duplicate threads. At the very least this can waste time.
Don’t have an account yet? Register yourself now and be a part of our community!