Sum Range in VBA

Excel VBA does not have a built-in function for adding all the numbers in a range of cells. If we want to add all the numeric values in a range of cells in Excel VBA, we must use the WorksheetFunction property to call the Excel SUM function into a VBA procedure.

The WorksheetFunction is a property of the Application object. The SUM worksheet function is available as a method of the Application object.

This tutorial shows you seven methods for summing a range in Excel VBA.

Method #1: Sum a Particular Cell Range

We can use Excel VBA to sum the numbers in a specific cell range.

Let’s look at the following dataset showing the sales figures of particular desktop brands.

We want to use Excel VBA to add the sales numbers in cell range C2:C6 and display the result in cell C7.

We use the below steps:

  1. Open the worksheet containing the dataset.
  2. Click Developer >> Code >> Visual Basic to open the Visual Basic Editor.

Alternatively, you can open the Visual Basic Editor by pressing the shortcut Alt + F11.

  1. Click Insert >> Module in the Visual Basic Editor to insert a module.
  1. Type the following sub-procedure in the module.
  1. Save the sub-procedure and the workbook as a Macro-Enabled Workbook.
  2. Place the cursor anywhere in the sub-procedure and press F5 to run the code.
  3. Click the View Microsoft Excel button on the toolbar to switch to the active worksheet containing the dataset.

You can also move to the active worksheet by pressing the shortcut Alt + F11.

You can see that the range C2:C6 has been summed, and the result is displayed in cell C7.

Graphical user interface, application, table, Excel

Description automatically generated

Method #2: Sum an Entire Column

To sum an entire column, for example, column B, and display the result in cell C1, use the sub-procedure below:

Method #3: Sum an Entire Row

To sum an entire row, for example, row 2, and display the result in cell B1, use the sub-procedure below:

Method #4: Sum Range With Criteria

We can sum a range specified by a given condition or criteria in VBA by using the WorksheetFunction property to call the Excel SUMIF function.

The SUMIF function adds the cells specified by a given condition or criteria.

Let’s consider the dataset below that shows the sales numbers of particular desktop brands:

We want to add only the sales numbers that are at least 1, 500 and display the result in cell E3.

We use the below steps:

  1. Open the worksheet containing the dataset.
  2. Click Developer >> Code >> Visual Basic to open the Visual Basic Editor.
Graphical user interface, application

Description automatically generated

Alternatively, you can open the Visual Basic Editor by pressing the shortcut Alt + F11.

  1. Click Insert >> Module in the Visual Basic Editor to insert a module.
Graphical user interface, application

Description automatically generated
  1. Type the following sub-procedure in the module.
  1. Save the sub-procedure and the workbook as a Macro-Enabled Workbook.
  2. Place the cursor anywhere in the sub-procedure and press F5 to run the code.
  3. Click the View Microsoft Excel button on the toolbar to switch to the active worksheet containing the dataset.
Graphical user interface, application, Word

Description automatically generated

You can also go to the active worksheet by pressing the shortcut Alt + F11.

You can see that only those sales numbers that are at least $1,500 have been added, and the result is displayed in cell E3.

Graphical user interface, application, table, Excel

Description automatically generated

Method #5: Sum a Selected Range

We can use Excel VBA, to sum up, a selected range.

We use the following dataset to show how Excel VBA can sum a selected range.

We proceed as below:

  1. Open the worksheet containing the dataset.
  2. Use the steps shown in Method #4 to open the Visual Basic Editor and create a new module.
  3. Type the following sub-procedure in the module. The comments describe what the code is doing:
  1. Save the sub-procedure and the workbook as a Macro-Enabled Workbook.
  2. Click the View Microsoft Excel button on the toolbar to switch to the active worksheet containing the dataset.
Graphical user interface, application, Word

Description automatically generated

You can also switch to the active worksheet by pressing the shortcut Alt + F11.

  1. In the active worksheet, select range C2:C4.
Table

Description automatically generated
  1. Click Developer >> Code >> Visual Basic to switch to the Visual Basic Editor.
Graphical user interface, application

Description automatically generated

Alternatively, press the shortcut Alt + F11.

  1. Place the cursor anywhere in the procedure and press F5 to run the code.

The sub-procedure opens the active worksheet and displays the sum of the selected range in a message box.

Method #6: Sum a Range Object

We can assign a cell range to the Range object variable and then use the Range object together with WorksheetFunction to calculate the sum of the range.

We use the following dataset to show how this method works.

Table, Excel

Description automatically generated

We use the following steps:

  1. Open the worksheet containing the dataset.
  2. Use the steps shown in Method #4 to open the Visual Basic Editor and insert a module.
  3. Type the following sub-procedure in the module. The comments explain what the code is doing.
  1. Save the sub-procedure and the workbook as a Macro-Enabled Workbook.
  2. Place the cursor anywhere in the sub-procedure and press F5 to run the code.
  3. Click the View Microsoft Excel button on the toolbar to switch to the active worksheet containing the dataset.

You can also switch to the active worksheet by pressing the shortcut Alt + F11.

You can see that the range C2:C6 has been summed, and the result is displayed in cell C7.

Graphical user interface, application, table, Excel

Description automatically generated

Method #7: Sum Multiple Ranges

Sometimes we may need to sum multiple ranges using Excel VBA.

Let’s look at the dataset below showing the sales of desktop computers for 2020 and 2021.

We want to add the sales of 2020 and 2021 and display the result in cell F3.

We use the steps below:

  1. Open the worksheet containing the dataset.
  2. Follow the steps shown in Method #4 to open the Visual Basic Editor and insert a module.
  3. Type the following code in the module. The comments explain what the code is doing:
  1. Save the sub-procedure and the workbook as a Macro-Enabled Workbook.
  2. Place the cursor anywhere in the sub-procedure and press F5 to run the code.
  3. Click the View Microsoft Excel button on the toolbar to switch to the active worksheet containing the dataset.

You can also switch to the active worksheet by pressing the shortcut Alt + F11.

You can see that the ranges C2:C6 and D2:D6 have been added, and the result is shown in cell F3.

Graphical user interface, application, table, Excel

Description automatically generated

Conclusion

Excel VBA does not have a built-in function for adding ranges, but we can use the WorksheetFunction property of the Application object to call the Excel SUM function into VBA procedures. This tutorial has shown seven methods of adding a range or ranges in Excel VBA. We hope that you found the tutorial helpful.