[Excel VBA] Two Ways to Insert a SUM Total Below the Last Row of Data

In invoices or sales lists, it is very common to want to automatically calculate the total of a column and insert it immediately after the last data entry. Since the number of data rows changes every time, you need a macro that can dynamically find the last row.

This article explains two representative approaches in VBA to achieve this: adding a row below the last data cell and inputting the total sum.

Frequent Technique: Finding the Last Cell with End(xlDown)

The key to both approaches is the code Range("B2").End(xlDown).

This performs the same action as selecting cell B2 and pressing Ctrl + Down Arrow on your keyboard. It is a standard technique for retrieving the bottom-most cell in a continuous range of data.

Method 1: Using a Loop (Adding Cells One by One)

The first method involves looping through the specified range of cells one by one and adding their values together.

Code and Explanation

Sub SumColumnByLooping()
    ' Declare variables
    Dim targetColumnStart As Range
    Dim dataRange As Range
    Dim cell As Range
    Dim totalValue As Double
    
    ' Set the starting cell for the total (e.g., C2)
    Set targetColumnStart = ThisWorkbook.Worksheets("Sheet1").Range("C2")
    
    ' --- 1. Define the dynamic range to sum ---
    Set dataRange = Range(targetColumnStart, targetColumnStart.End(xlDown))
    
    ' --- 2. Add cell values using a For Each loop ---
    totalValue = 0 ' Initialize total value
    For Each cell In dataRange
        ' Only add if the cell value is numeric
        If IsNumeric(cell.Value) Then
            totalValue = totalValue + cell.Value
        End If
    Next cell
    
    ' --- 3. Input the total value one cell below the last row ---
    targetColumnStart.End(xlDown).Offset(1, 0).Value = totalValue

    MsgBox "Total value entered using loop processing."
End Sub

This method is good for understanding the basic flow of VBA processing because it is clear what is happening inside the For Each loop. However, it has the disadvantage of being very slow if there are thousands or tens of thousands of rows of data.

Method 2: Using WorksheetFunction (Recommended)

Next is a faster and smarter method that calls Excel’s SUM function directly from VBA.

Code and Explanation

Sub SumColumnByWorksheetFunction()

    ' Declare variables
    Dim targetColumnStart As Range
    Dim dataRange As Range
    Dim totalValue As Double
    
    ' Set the starting cell for the total (e.g., C2)
    Set targetColumnStart = ThisWorkbook.Worksheets("Sheet1").Range("C2")

    ' --- 1. Define the dynamic range to sum ---
    Set dataRange = Range(targetColumnStart, targetColumnStart.End(xlDown))
    
    ' --- 2. Calculate total instantly using WorksheetFunction.Sum ---
    totalValue = WorksheetFunction.Sum(dataRange)
    
    ' --- 3. Input the total value one cell below the last row ---
    targetColumnStart.End(xlDown).Offset(1, 0).Value = totalValue
    
    MsgBox "Total value entered using WorksheetFunction."

End Sub

With just one line, WorksheetFunction.Sum(dataRange), you get the same result as the loop method.

Internally, this uses Excel’s highly optimized calculation engine, so the processing is completed instantly regardless of the amount of data.

Summary

Here is a comparison of the two methods for entering a total value at the end of a data column.

Method	Pros	Cons
Loop Processing	The logic is easy to understand step-by-step.	It is very slow if there is a large amount of data.
WorksheetFunction	Overwhelmingly fast. The code is concise.	None.

Conclusion:

Unless there is a special reason not to, I strongly recommend using Method 2 (WorksheetFunction.Sum).

While loop processing is good practice for learning the basics of VBA, in practical situations where performance is required, you should actively use WorksheetFunction to create fast and efficient macros.