# How to sum various rows in vba

#### Haikal

##### New Member
I am trying to the write the formula to sum up a row. My last column always varies but the first column is fixed. .
The formula i have is:
Code:
``sFormula = "=SUM(RC[-6]:RC[-1])"``

but is doesn't work for all exercises as some times the total column is "D" or "H"..

what i need is a function like this:
Code:
``sFormula = "=SUM(RC[Number or columns - 1]:RC[-1])"``

Example:
Column: A B C D E TOTAL
NRow 1: 00:00 3 4 5 6 18
NRow 2: 00:05 4 5 9
NRow 3: 00:10 7 9 11 27

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You can use an absolute R1C1 reference instead of a relative reference.

If your "fixed" column is Column B, then use ...
Code:
``sFormula = "=SUM(RC2:RC[-1])"``

the Sum column varies..

i want a tool to select the last column, and then apply this formula to the last row.

the Sum column varies..

i want a tool to select the last column, and then apply this formula to the last row.

If the formula is placed in the cell to the right of the last value in the row, it will sum the values on the same row from column B to the last column.

Here's an example that will enter that formula on the ActiveCell's row....

Code:
``````Sub AddSumToThisRow()
Dim lLastCol As Long

With ActiveCell
lLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column
If lLastCol > 1 Then _
Cells(.Row, lLastCol + 1).FormulaR1C1 = "=SUM(RC2:RC[-1])"
End With
End Sub``````

it works but only for one row, what about making it for all rows? (i.e till the last row)

it works but only for one row, what about making it for all rows? (i.e till the last row)

Code:
``````Sub AddSumToEachRow()
Dim lLastCol As Long, lLastRow As Long
Dim lCurrentRow As Long

Const lFirstRow = 2

With ActiveSheet
lLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For lCurrentRow = lFirstRow To lLastRow
lLastCol = Cells(lCurrentRow, Columns.Count).End(xlToLeft).Column
If lLastCol > 1 Then _
Cells(lCurrentRow, lLastCol + 1).FormulaR1C1 = "=SUM(RC2:RC[-1])"
Next lCurrentRow
End With
End Sub``````

Great! thanks alot!

Replies
2
Views
138
Replies
16
Views
314
Replies
4
Views
406
Replies
0
Views
150
Replies
3
Views
168

1,196,181
Messages
6,013,909
Members
441,794
Latest member
Deepakjaat31

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back