Excel VBA to Sum Range of Cells in Column

In this article, we will learn how we can use Excel VBA Sum Range of Cells in Column. Summing up a range of cells in a column is a very common task in Financial Analysis, Sales Analysis, Inventory Management, Budgeting, Project Management, Academic grading, and many other sectors. Here, we will see how we can sum up a range of cells in many situations with proper illustrations and associated VBA Codes. So, let’s start our journey.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

How to Launch VBA Editor in Excel

In this section, we are going to demonstrate how to launch VBA Editor & create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.

• First, we go to the Developer tab.
• Then we will select Visual Basic.

Then a new window will pop up. In this window, you can double-click (left) on the Sheet name on the left panel to open the Sheet module.

If you write code on a particular sheet module, the code will only work on that worksheet. However, if you want to write code that can work across all the worksheets, you need to write the code in a new module. To insert a new module, follow these steps,

• First, we will select Insert.
• Then we are going to select Module.
• A new Module will be created.

Excel VBA to Sum Range of Cells in Column: 6 Different Cases

In this section, we will demonstrate the 6 most common cases of summing up a range of cells in Excel with appropriate illustrations. Let’s explore the examples one by one.

1. Sum Range of Cells in Column

In this example, we are going to see how we can get the sum of a range of cells in a specific column in Excel using VBA. Here, I have taken a dataset that consists of no. of goals scored by Lionel Messi in each season from 2011-12 to 2021-22 in the UEFA Champions League.

Now, we will use a VBA Code to sum the range C5:C15 and put the result on cell C16. To do that, follow the steps below.

Steps:

• First, open the VBA window by clicking Alt+F11 and double-click (left) on the Sheet name to open the Sheet module.
• Now, write the following vba code.

Code Syntax:

Sub SumRangeInColumn()
Range("C16").Value = Application.WorksheetFunction.Sum(Range("C5:C15"))
End Sub

• Now, run the code by pressing the F5 or clicking the play button.
• As a result, we will see the total sum of the range of cells of that column in cell C16.

2. Summing Up an Entire Column

Suppose, you want to sum the entire C column and display the sum value in cell E5 in the dataset that was provided in example 1.

You can do that using the following VBA Code.

 Code Syntax:

Sub SumEntireColumn()
Range("E5").Value = Application.WorksheetFunction.Sum(Range("C:C"))
End Sub

If you paste the code in the Sheet Module and run it from there, you will see the sum of the entire column C in cell E5.

3. Sum Column with Criteria

In this example, we will see how we can sum up a column with criteria. For illustration, I have taken another data set where there is sales information on different category products such as Red, Blue, and Green.

We want to sum up the “Sales” column based on the criteria that only the Red category will be taken into account. The result will be displayed on cell F5. To do that, we run the following VBA code.

Code Syntax:

Sub SumCriteria()
Range("F5").Value = Application _
.WorksheetFunction _
.SumIf(Range("C5:C14"), "Red", Range("D5:D14"))
End Sub

If we run the code, we will have the sum of the total sales of the red category items.

4. Sum Selected Range in Column

Summing a Selected Range Using Worksheet SUM Function.

In this example, we will first select a range from a column and then calculate the sum of the selected cells and display it on a MsgBox. The code for accomplishing this task is given below.

Code Synatx:

Sub SumSelection()
Dim selection As Range
Set selection = Application.InputBox("Select a Range", Type:=8)
Sum = 0
For Each cell In selection
Sum = Sum + cell.Value
Next cell
MsgBox ("Total Sum of the Selected Range = " & Sum)
End Sub

Sub SumSelection()

Dim selection As Range

Set selection = Application.InputBox("Select a Range", Type:=8)

Sum = 0

For Each cell In selection
Sum = Sum + cell.Value
Next cell

MsgBox ("Total Sum of the Selected Range = " & Sum)

Now, follow the steps below.

Steps:

• Run the code by pressing F5 or clicking the Play As a result, an InputBox will open and ask for selecting a range. I selected D5:D7 and clicked OK.

• As a result, I got the sum of the selected range which is 600.

5. Sum Multiple Ranges

In this example, I will show you how we can sum multiple ranges from two different columns. For illustration, I have taken a dataset with the no. of goals of Messi and Ronaldo scored in different seasons of the UEFA Champions League in two columns.

Our goal is to sum two ranges C5:C15 & D5:D15 and write it down in cell C17. To do that, follow the steps below.

Steps:

• Go to the sheet module and paste the following code.

Code Syntax:

Sub SumMultipleRange()
Dim range1, range2 As Range
Set range1 = Range("C5:C15")
Set range2 = Range("D5:D15")
sum1 = 0
For Each cell In range1
 sum1 = sum1 + cell.Value
Next cell
sum2 = 0
For Each cell In range2
sum2 = sum2 + cell.Value
Next cell
Total_sum = sum1 + sum2
Range("c17").Value = Total_sum
End Sub

Sub SumMultipleRange()

Dim range1, range2 As Range

Set range1 = Range("C5:C15")
Set range2 = Range("D5:D15")

sum1 = 0

For Each cell In range1
 sum1 = sum1 + cell.Value
Next cell

sum2 = 0

For Each cell In range2
sum2 = sum2 + cell.Value
Next cell

Total_sum = sum1 + sum2

Range("C17").Value = Total_sum

• Now if we run the code, we will get the total sum of the two selected ranges in the cell.

6. VBA Sum All Cells Above in a Column

In this example, we will select a cell and determine the sum of all the cells above the active cell and write down the sum on the active cell.

To do that, we can use the following VBA code.

Code Syntax:

Sub SumAboveCells()
Dim First_cell As String
Dim Last_cell As String
Dim rng As Range
On Error GoTo error_Handler
First_cell = selection.End(xlUp).End(xlUp).Address
Last_cell = selection.End(xlUp).Address
Set rng = Range(First_cell & ":" & Last_cell)
ActiveCell = WorksheetFunction.Sum(rng)
Exit Sub
error_Handler:
MsgBox "Please select a valid range"
End Sub

First_cell = selection.End(xlUp).End(xlUp).Address

Last_cell = selection.End(xlUp).Address

Set rng = Range(First_cell & ":" & Last_cell)

ActiveCell = WorksheetFunction.Sum(rng)

Now, if we run the code after selecting any cell, it will return the sum of all the above cells in that active cell.

How to Use Worksheet SUM Function for Summing an Entire Row in Excel VBA

Suppose, you want to sum the entire 6th row and display the sum value in cell F5. For illustration, I have taken the dataset below.

Here, I have populated only two cells (C6 & D6) with numerical data. Hence the sum of the entire row should be 8+12=20. We can calculate this value using the following VBA Code.

Code Syntax:

Sub sumRow6()
    Range("F5") = Application.WorksheetFunction.Sum(Range("6:6"))
End Sub

If we run the code from the Sheet Module, we will get the 20 in cell F5.

Now, if I populate any other cell in row 6 (Say, put 30 on E6) and again run the code, we will get 50 in cell F5. Note that, you must re-run the code to get the updated sum of the entire row 6.

Things to Remember

• If you change any cell value, you must re-run the provided VBA codes to get the latest sum of all the cells in a column.
• All of the codes in this article are effective for the active worksheet. As a result, it is always preferable to run those codes from the worksheet’s individual Sheet Module. When running the code from a new module, you must be cautious about the active worksheet.

Conclusion

That is the end of this article regarding Excel VBA sum range of cells in column. Hopefully, after reading this post, you can confidently apply the presented VBA codes in your circumstances and obtain the required results. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exceldemy for more exciting articles on Excel.