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!