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:

Open the worksheet containing the dataset.
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.

Click Insert >> Module in the Visual Basic Editor to insert a module.

Type the following sub-procedure in the module.

Sub sumRange()
    Range("C7") = Application.WorksheetFunction.Sum(Range("C2:C6"))
End Sub

Sub sumRange()

Range("C7") = Application.WorksheetFunction.Sum(Range("C2:C6"))

End Sub

Save the sub-procedure and the workbook as a Macro-Enabled Workbook.
Place the cursor anywhere in the sub-procedure and press F5 to run the code.
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:

Sub sumColumnB()
    Range("C1") = Application.WorksheetFunction.Sum(Range("B:B"))
End Sub

Sub sumColumnB()

Range("C1") = Application.WorksheetFunction.Sum(Range("B:B"))

End Sub

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:

Sub sumColumnB()
    Range("B1") = Application.WorksheetFunction.Sum(Range("2:2"))
End Sub

Sub sumColumnB()

Range("B1") = Application.WorksheetFunction.Sum(Range("2:2"))

End Sub

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:

Open the worksheet containing the dataset.
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.

Click Insert >> Module in the Visual Basic Editor to insert a module.

Type the following sub-procedure in the module.

Sub sumRangeIf()
    Range("E3") = Application.WorksheetFunction.SumIf(Range("C2:C6"), ">=1500")
End Sub

Sub sumRangeIf()

Range("E3") = Application.WorksheetFunction.SumIf(Range("C2:C6"), ">=1500")

End Sub

Save the sub-procedure and the workbook as a Macro-Enabled Workbook.
Place the cursor anywhere in the sub-procedure and press F5 to run the code.
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.

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:

Open the worksheet containing the dataset.
Use the steps shown in Method #4 to open the Visual Basic Editor and create a new module.
Type the following sub-procedure in the module. The comments describe what the code is doing:

Sub sumSelectedRange()
    Dim selectedRange As Range ' Declare object variable
    Dim rngSum As Long ' Declare variable of Long data type
    Set selectedRange = Selection 'Assign the selected range to the object variable using the Set keyword
    rngSum = WorksheetFunction.Sum(Range(selectedRange.Address)) 'Use the selected range in a formula
    MsgBox rngSum 'Display the sum of the range in a message box
End Sub

Sub sumSelectedRange()

Dim selectedRange As Range ' Declare object variable

Dim rngSum As Long ' Declare variable of Long data type

Set selectedRange = Selection 'Assign the selected range to the object variable using the Set keyword

rngSum = WorksheetFunction.Sum(Range(selectedRange.Address)) 'Use the selected range in a formula

MsgBox rngSum 'Display the sum of the range in a message box

End Sub

Save the sub-procedure and the workbook as a Macro-Enabled Workbook.
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.

In the active worksheet, select range C2:C4.

Table

Description automatically generated

Click Developer >> Code >> Visual Basic to switch to the Visual Basic Editor.

Alternatively, press the shortcut Alt + F11.

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:

Open the worksheet containing the dataset.
Use the steps shown in Method #4 to open the Visual Basic Editor and insert a module.
Type the following sub-procedure in the module. The comments explain what the code is doing.

Sub sumRangeObject()
    Dim rng As Range 'Declare the object variable
    Set rng = Range("C2:C6") 'assign the range of cells to the object variable using the Set keyword
    
    Range("C7") = WorksheetFunction.Sum(rng) 'assigns the sum of the range to cell C7
    
    Set rng = Nothing 'release the range object
End Sub

Sub sumRangeObject()

Dim rng As Range 'Declare the object variable

Set rng = Range("C2:C6") 'assign the range of cells to the object variable using the Set keyword

Range("C7") = WorksheetFunction.Sum(rng) 'assigns the sum of the range to cell C7

Set rng = Nothing 'release the range object

End Sub

Save the sub-procedure and the workbook as a Macro-Enabled Workbook.
Place the cursor anywhere in the sub-procedure and press F5 to run the code.
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.

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:

Open the worksheet containing the dataset.
Follow the steps shown in Method #4 to open the Visual Basic Editor and insert a module.
Type the following code in the module. The comments explain what the code is doing:

Sub sumMultipleRanges()
    Dim rng1 As Range ' Declare object variable
    Dim rng2 As Range ' Declare object variable
    Set rng1 = Range("C2:C6") 'assign the range of cells
    Set rng2 = Range("D2:D6") 'assign the range of cells
    Range("F3") = WorksheetFunction.Sum(rng1, rng2) 'use the ranges in the formula
    
    Set rng1 = Nothing 'release the range object
    Set rng2 = Nothing 'release the range object
End Sub

Sub sumMultipleRanges()

Dim rng1 As Range ' Declare object variable

Dim rng2 As Range ' Declare object variable

Set rng1 = Range("C2:C6") 'assign the range of cells

Set rng2 = Range("D2:D6") 'assign the range of cells

Range("F3") = WorksheetFunction.Sum(rng1, rng2) 'use the ranges in the formula

Set rng1 = Nothing 'release the range object

Set rng2 = Nothing 'release the range object

End Sub

Save the sub-procedure and the workbook as a Macro-Enabled Workbook.
Place the cursor anywhere in the sub-procedure and press F5 to run the code.
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.

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 1,260