# Excel VBA to Sum Range of Cells in Column

Get FREE Advanced Excel Exercises with Solutions!

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.

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

🔎 How Does the Code Work?

Here, the WorksheetFunction method of the Application object is used to sum the range C5:C15 and the sum value is stored in cell C16.

• 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``````

🔎 How Does the Code Work?

• Here, the WorksheetFunction SumIf is used to carry out the summation of the column D5:D14. The first argument is the criteria range (C5:C14 )
• The Second argument is the Criteria (“Red”)
• The third argument is the sum range (D5:D14)

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``````

🔎 How Does the Code Work?

``Sub SumSelection()``

Creating a subroutine named SumSelection.

``Dim selection As Range``

Declaring a Range type variable named selection.

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

Opening an InputBox and taking a range as input and assigning it to the variable selection.

``Sum = 0``

Setting the value of variable Sum to 0.

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

Summing up values of all the cells and storing them in the sum variable.

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

Displaying the sum of the selected range in a MsgBox.

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``````

🔎 How Does the Code Work?

``Sub SumMultipleRange()``

Created a subroutine named SumMultipleRange.

``Dim range1, range2 As Range``

Declared two Range type variables named range1 and range2.

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

Assigning the required ranges to the declared variables.

``sum1 = 0``

Taking another variable named  sum1 and initializing its value to zero.

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

Applying For Each loop to sum up all the values in range1.

``sum2 = 0``

Taking another variable named  sum2 and initializing its value to zero.

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

Applying For Each loop to sum up all the values in range2.

``Total_sum = sum1 + sum2``

Taking another variable named Total_sum and assigning its value to  sum1 + sum2.

``Range("C17").Value = Total_sum``

Storing the value of Total_sum  in cell C17.

• 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
Set rng = Range(First_cell & ":" & Last_cell)
ActiveCell = WorksheetFunction.Sum(rng)
Exit Sub
error_Handler:
MsgBox "Please select a valid range"
End Sub``````

🔎 How Does the Code Work?

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

Taking the address of the topmost cell from the active cell.

``Last_cell = selection.End(xlUp).Address``

Taking the address of the bottommost cell from the active cell.

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

Setting the range from First_cell to Last_cell.

``ActiveCell = WorksheetFunction.Sum(rng)``

Writing the Sum of the rng on the active cell using WorksheetFunction Sum.

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 Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  