How to Sum a Range of Cells in a Row Using Excel VBA (6 Methods)

To demonstrate our methods, we have a dataset with three columns: “Fruit,” “March Sale,” and “April Sale.”

Method 1 – Using the FormulaR1C1 Property to Sum Range of Cells

Steps:

• Press ALT + F11 to bring up the VBA Window.

Alternatively, you can do this from the Developer tab >>> select Visual Basic.

• From Insert >>> select Module.

This will show the VBA Module window. We’ll type our code here.

• Enter the following code in the VBA Module:

Sub SumInRow()
Dim x, rTotal As Range
Set x = Range("B5:D" & Range("B" & Rows.Count).End(xlUp).Row)
Set rTotal = x.Offset(, 3).Resize(x.Rows.Count, 1)
With rTotal
    .FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
    .Value = .Value
End With
End Sub

• Save the code.
• Click anywhere inside the code.
• Press the Run button.

This will execute our code, and the added values will be seen in column E.

Method 2 – Using the VBA Evaluate Function to Sum a Range of Cells

Steps:

• As shown in method 1, bring up the VBA Module and enter this code:

Sub SumInRowEvaluate()
Dim x As Range
Set x = Range("C5:D10")
For y = 1 To x.Rows.Count
    x.Rows(y).Cells(1, 3).Value = Evaluate("SUM(" & x.Rows(y).Address & ")")
Next y
End Sub

• Save the Module and press the Run button.

Note: This time, we didn’t click inside our code; hence, the Macros dialog box will appear.

• Select “SumInRowEvaluate” and press Run.

We’ll use VBA to sum the range of cells in rows.

Method 3 – Using the Dynamic VBA Code to Find the Output Location to Place a Sum Automatically

Steps:

• As shown in method 1, bring up the VBA Module and enter this code:

Sub SumInRowDynamic()
Dim x As Long, y As Long
x = Rows(4).Find("Total Sale", , , 1).Column - 2
y = Cells(Rows.Count, 2).End(xlUp).Row
    With Range(Cells(5, x + 2), Cells(y, x + 2))
        .Formula = "=SUM(RC[-" & x & "]:RC[-1])"
        .Value = .Value
    End With
End Sub

• Save the code.
• Click anywhere inside the code.
• Press the Run button.

This will execute our code, and we’ll see the added values in E5:E10. This is what the final step should look like.

Method 4 – Summing Rows Based on the Dynamic Range of Columns and Display the Formula

Steps:

• As shown in method 1, bring up the VBA Module and enter this code:

Sub SumRowShowFormula()
    Dim x As Long
    For x = 5 To Cells(Rows.Count, "B").End(xlUp).Row
        Cells(x, "E").Formula = Replace("=sum(C@:D@)", "@", x)
    Next
End Sub

• Save the code.
• Click anywhere inside the code.
• Press the Run button.

This will execute our code, and we’ll see the added values in E5:E10. We can see the SUM function if we select any cells from the output.

Method 5 – Inserting the VBA WorksheetFunction.Sum

Steps:

• As shown in method 1, bring up the VBA Module and enter this code:

Sub SumRowWFunction()
    Dim x As Long
    For x = 5 To Cells(Rows.Count, "B").End(xlUp).Row
        Cells(x, "E") = WorksheetFunction.Sum(Range("C" & x & ":D" & x))
    Next
End Sub

• Save and close the Module.
• From the Developer tab >>> select Macros.

The Macro dialog box will appear.

• Select “SumRowWFunction” from the list.
• Press the Run button.

We’ve shown yet another code to add a range of cells in the row.

Method 6 – Finding the Range of Cells to Sum in a Row

Steps:

• As shown in method 1, bring up the VBA Module and enter this code:

Sub SumInRowsDynamicRange2()
Dim wSheet As Worksheet
Dim x As Long
Dim y As Long
Application.ScreenUpdating = False
Set wSheet = Sheets("dynamic2")
x = wSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
y = wSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
wSheet.Cells(5, y).Resize(x - 4).FormulaR1C1 = "=SUM(RC3:RC[-1])"
Application.ScreenUpdating = True
End Sub

• Save the Module and Run it.

We’ve achieved our goal of a sum range of cells in the row.

Practice Section

We’ve provided practice datasets in the Excel file.

Download Practice Workbook