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

We’re going to show you 6 easy methods of using Excel VBA to sum the range of cells in a row. To demonstrate our methods, we’ve picked a dataset with 3 columns: “Fruit”. “March Sale”, and “April Sale”.

Table of Contents hide

Download Practice Workbook

6 Ways to Sum Range of Cells in Row Using Excel VBA

1. Using FormulaR1C1 Property to Sum Range of Cells

2. Use of VBA Evaluate Function to Sum Range of Cells

3. Dynamic VBA Code to Find Output Location to Place Sum Automatically

4. Sum Rows Based on Dynamic Range of Columns & Display the Formula

5. Inserting VBA WorksheetFunction.Sum

6. Find Range of Cells to Sum in Row

Practice Section

Conclusion

Download Practice Workbook

VBA to Sum Range of Cells in a Row.xlsm

6 Ways to Sum Range of Cells in Row Using Excel VBA

1. Using FormulaR1C1 Property to Sum Range of Cells

In the first method, we’re going to use R1C1-style notation inside a With Statement to create a VBA code to sum the range of cells in rows. We’re going to show the Total Sale for each Fruit in column E. That means, E5 = C5 + D5 and so on. We’ll do this in all of the methods with different VBA codes.

Steps:

Firstly, press ALT + F11 to bring up the VBA Window.

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

Secondly, from Insert >>> select Module.

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

Thirdly, type the following code in 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

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure SumInRow.
Secondly, declaring our variable types.
Thirdly, we’re setting our cell range for sum operation.
Finally, with the FormulaR1C1 property, we’re adding the row totals.
Here, RC[-2] means 2 columns left, and RC[-1] means 1 column left.
Basically, we’re telling E5 = C5 + D5 here.

Then, Save the code.
After that, click anywhere inside the code.
Finally, press the Run button.

This will execute our code, and we’ll see the added values in column E.

2. Use of VBA Evaluate Function to Sum Range of Cells

In this method, we’re going to use the VBA Evaluate method to sum the range of cells in the rows. Moreover, we’ll use For Next Loop and manually type our data range in this code.

Steps:

Firstly, as shown in method 1, bring up the VBA Module and type 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

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure SumInRowEvaluate.
Secondly, declaring our variable types.
Thirdly, we’re setting our cell range for sum operation.
Finally, with a For Next Loop, we’re adding the row totals.

Secondly, 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.

Finally, select “SumInRowEvaluate” and press Run.

Thus, we’ll use VBA to sum the range of cells in rows.

3. Dynamic VBA Code to Find Output Location to Place Sum Automatically

In this section, we’ll not set our output location manually. We’re gonna use a dynamic code to look for our output cells. Here, we’ll use the “VBA Find method” to locate our output column.

Steps:

Firstly, as shown in method 1, bring up the VBA Module and type 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

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure SumInRowDynamic.
Secondly, declaring our variable types.
Thirdly, we’re searching for our output column name “Total Sale” in the dataset using the VBA Find method.
Then, we’re setting our cell range for sum operation.
Finally, we’re finding the row totals inside the With Statement.

Secondly, Save the code.
Thirdly, click anywhere inside the code.
Finally, press the Run button.

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

Similar Readings

4. Sum Rows Based on Dynamic Range of Columns & Display the Formula

In this fourth method, we’re going to display the SUM formula in the cells after we execute our code. Without further ado, let’s jump into the action.

Steps:

Firstly, as shown in method 1, bring up the VBA Module and type 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([email protected]:[email protected])", "@", x)
    Next
End Sub

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure SumRowShowFormula.
Secondly, declaring our variable types.
Thirdly, numbers in our data start from row 5. Hence, we’ve put 5 in our code.
Then, we’re finding the last position of our row using the Range End property.
After that, the VBA Sum function will add our values.
Finally, we’re using the Range Replace method to show our formula in cells.

Secondly, Save the code.
Thirdly, click anywhere inside the code.
Finally, press the Run button.

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

5. Inserting VBA WorksheetFunction.Sum

In this section, we’ll use the WorksheetFunction object to call the Excel SUM function to add the range of cells in the row.

Steps:

Firstly, as shown in method 1, bring up the VBA Module and type 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

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure SumRowWFunction.
Secondly, declaring our variable types.
Thirdly, numbers in our data start from row 5. Hence, we’ve put 5 in our code.
Then, we’re finding the last position of our row using the Range End property.
After that, the Worksheet Sum function will add our values.

Secondly, Save and close the Module.
Thirdly, from the Developer tab >>> select Macros.

The Macro dialog box will appear.

Select “SumRowWFunction” from the list.
Finally, press the Run button.

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

6. Find Range of Cells to Sum in Row

For the last method, we need to define our starting row and use the Range Resize property. Everything else will be automatic in this code. Moreover, we’re fixing our Sheet name here.

Steps:

Firstly, as shown in method 1, bring up the VBA Module and type 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

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure SumInRowsDynamicRange2.
Secondly, declaring our variable types.
Thirdly, we’re setting our Sheet name “dynamic2”.
After that, we’re finding our dataset range.
Finally, we’re adding the values in rows.

Finally, Save the Module and Run it.

In conclusion, 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.

Conclusion

We’ve shown you methods using Excel VBA to sum range of cells in row. Thanks for reading, keep excelling!

2 Comments

EG Barber Jan 6, 2023 at 12:42 AM

I understand the VBA code to sum a dynamic row but I am having problems converting it to sum a column and insert the formula so that the total changes if the numbers change. Is there an example of this anywhere? Thanks
- Reply
  Rafiul Haq Jan 9, 2023 at 11:28 AM
  Hello, EG Barber thank you for your question. You can use the following code and paste inside your sheet (right click on the sheet, select View Code and paste the code there) to get an automatic VBA sum feature.
```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim our_cell_range As Range
Set our_cell_range = Range("C5:C10")
If Not Application.Intersect(our_cell_range, Range(Target.Address)) Is Nothing Then
Range("C11") = Application.WorksheetFunction.Sum(our_cell_range)
End If
End Sub
```
  The following animated image shows the solution in action.

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

Download Practice Workbook

6 Ways to Sum Range of Cells in Row Using Excel VBA

1. Using FormulaR1C1 Property to Sum Range of Cells

2. Use of VBA Evaluate Function to Sum Range of Cells

3. Dynamic VBA Code to Find Output Location to Place Sum Automatically

4. Sum Rows Based on Dynamic Range of Columns & Display the Formula

5. Inserting VBA WorksheetFunction.Sum

6. Find Range of Cells to Sum in Row

Practice Section

Conclusion

Related Articles

Rafiul Haq

Excel VBA to Delete Named Range (5 Suitable Examples)

How to Apply VBA to Open and Activate Excel Workbook

How to Check If Named Range Exists with Excel VBA

How to Create Histogram in Excel Using VBA (With Easy Steps)

Leave a reply Cancel reply