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

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.

- 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**.

**Read More:** **How to Sum Multiple Rows in Excel (4 Quick Ways)**

### 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**.

**Read More:** **How to Sum Between Two Numbers Formula in Excel**

### 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.

**Read More:** **How to Add Multiple Cells in Excel (6 Methods)**

**Similar Readings**

**How to Sum Filtered Cells in Excel (5 Suitable Ways)****[Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)****How to Sum Only Positive Numbers in Excel (4 Simple Ways)****Excel Sum If a Cell Contains Criteria (5 Examples)****How to Add Specific Cells in Excel (5 Simple Ways)**

### 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.

**Read More:** **How to Sum Rows in Excel (9 Easy Methods)**

### 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**.

**Read More:** **How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)**

### 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!

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

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 Codeand paste the code there) to get an automatic VBA sum feature.The following animated image shows the solution in action.