VBA Range Sum: Methods, Examples, and Common Errors

To sum a range in Excel VBA, use Application.WorksheetFunction.Sum and assign its result to a variable or cell. For example, total = Application.WorksheetFunction.Sum(Range("A1:A10")) adds the values in A1:A10; qualify the range with a worksheet when your code should work on a specific sheet.

What a VBA Range sum does

A VBA Range represents one or more worksheet cells. Passing a range to Excel’s SUM function asks Excel to total the numeric values in those cells, while VBA controls what happens to the result: it can be stored, displayed, compared, or written elsewhere.

This is different from typing a SUM formula into a worksheet cell. A worksheet formula recalculates as its referenced values change; a macro that calculates a total and writes the result as a value does not update that value automatically afterward. Choose based on whether the result should remain a live calculation or be produced as part of a macro task.

Use WorksheetFunction.Sum when VBA needs a numeric result from Excel’s SUM function.
Use Application.Sum when you want Excel’s function result returned as a Variant, which can be useful when checking for an error result.
Use a worksheet formula when the total should update automatically as the source cells change.

For ordinary ranges, both VBA approaches rely on Excel’s SUM behavior. SUM ignores blank cells and text stored in referenced cells, but an error value in the range can make the result an error. It adds numbers, not the text that merely looks like a number unless Excel has stored or converted that entry as numeric data.

Write a basic sum macro

Here is a complete example for a worksheet named Sales. It totals B2:B100 and puts the result in D2. The worksheet and ranges are explicitly qualified, so the macro does not depend on whichever sheet happens to be active.

Save a copy of the workbook before running a macro that changes cells. Macro actions cannot be undone with Ctrl+Z, even when the change seems small.

Sub TotalSales()
    Dim ws As Worksheet
    Dim total As Double

    ' Use the Sales sheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Sales")

    ' Add the values in B2:B100.
    total = Application.WorksheetFunction.Sum(ws.Range("B2:B100"))

    ' Write the result to D2.
    ws.Range("D2").Value = total
End Sub

In this example, ThisWorkbook means the workbook that contains the VBA code. If the code is stored in a different workbook from the data, you may need to refer to the workbook that holds the Sales sheet instead. The name inside Worksheets("Sales") must match the sheet tab name exactly; otherwise, VBA will report a subscript-out-of-range error.

To insert and run the macro in desktop Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code, then run the procedure from the editor. On Mac, use Developer > Visual Basic if available; the keyboard shortcut can depend on the keyboard and function-key settings. Excel for the web can open a workbook containing macros but cannot run VBA; use the desktop app.

WorksheetFunction.Sum and Application.Sum

Application.WorksheetFunction.Sum is a familiar choice when you expect a numeric answer. It returns the calculated sum as a value that can be assigned to a numeric variable or a cell. One important behavior is that if the Excel function produces an error, calling it through WorksheetFunction can raise a VBA runtime error rather than simply giving your code an ordinary number.

Application.Sum also calls Excel’s SUM calculation, but returns a Variant. That can make it easier to test the result before using it when source cells may contain errors. For example, a result can be checked with VBA’s IsError function. If the range is known to contain clean numeric data, the simpler WorksheetFunction form is usually clear and sufficient; if it may contain formula errors, plan explicitly for that possibility.

Neither method makes text entries numeric simply because they look like numbers. A value such as the text “12” stored in a referenced cell is generally ignored by SUM, while a numeric 12 is included. If a total looks too small, check whether imported values are actually numbers, whether the range covers all intended rows, and whether any cells contain errors.

Use a worksheet formula for a live total

If the total should change whenever someone edits the source cells, put a formula in the destination cell instead of storing a one-time result. With numbers 4, 7, 9, 10, and 20 in B2:B6, enter =SUM(B2:B6) in B7. The result is 50: 4 + 7 + 9 + 10 + 20.

The formula is evaluated and maintained by Excel, so it recalculates when the referenced values change, subject to the workbook’s calculation settings. A macro-written value does not have that relationship to its original range. You can also have VBA write a formula rather than a value, but that is a different outcome: the destination cell will contain a formula and continue to depend on its references.

Another practical distinction is auditability. A visible formula makes the source range apparent to someone inspecting the sheet. A macro can be more suitable when a larger process needs to calculate, export, or report a result, but users may need to inspect the VBA code to see how that result was created.

Sum a changing or selected range

For a range whose final row changes, VBA can find the last used row in a chosen column and build the range at run time. The example below assumes the data begins in B2, with no gaps that would make the last nonempty cell an unsuitable boundary.

Sub TotalChangingRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim total As Double

    ' Find the last nonempty cell in column B.
    Set ws = ThisWorkbook.Worksheets("Sales")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Do not include the heading if there are no data rows.
    If lastRow < 2 Then
        total = 0
    Else
        total = Application.WorksheetFunction.Sum(ws.Range("B2:B" & lastRow))
    End If

    ws.Range("D2").Value = total
End Sub

The End(xlUp) method moves upward from the bottom of column B to find the last nonempty cell. It is a common way to locate the end of a simple list, but it is not a universal definition of the last record. If the data column has blanks in its final rows, or if unrelated content appears farther down the column, the detected boundary may not match the records you intend to total. For structured data, an Excel Table can provide a clearer boundary and expand as rows are added.

You can also sum a selected range with Selection, but that makes the result depend on what the user has highlighted. This is convenient for a one-off tool, yet fragile in an automated procedure: the wrong selection can silently produce the wrong total. For repeatable work, specify the worksheet and range in the code or validate the selection before calculating.

Sum values that meet a condition

When you need a subtotal for matching records, use Excel’s SUMIF function through VBA rather than summing every value and filtering manually. For example, suppose regions are in A2:A100 and sales amounts are in B2:B100. This macro adds the sales values only for rows whose region is North.

Sub TotalNorthSales()
    Dim ws As Worksheet
    Dim total As Double

    ' Match each region in column A and total the corresponding value in B.
    Set ws = ThisWorkbook.Worksheets("Sales")
    total = Application.WorksheetFunction.SumIf( _
        ws.Range("A2:A100"), "North", ws.Range("B2:B100"))

    ws.Range("D2").Value = total
End Sub

The criteria range and sum range should have corresponding rows. In this example, A2 is paired with B2, A3 with B3, and so on. For multiple conditions, Excel provides SUMIFS; VBA can call it through Application.WorksheetFunction.SumIfs. Check the argument order in Excel’s function help when adapting it, because SUMIFS puts the sum range before its condition-range and condition pairs.

Common problems and how to diagnose them

A sum macro often fails because of range references or data quality rather than the SUM function itself. Check these issues before rewriting the calculation:

Wrong active sheet: Unqualified references such as Range("B2:B100") refer to the active sheet. Qualify them as ws.Range("B2:B100") when the target sheet is known.
Misspelled sheet name: Confirm the worksheet tab name matches the name used in the code, including spaces.
Values stored as text: Imported data may appear numeric but be stored as text. Check the cells’ values and convert the data appropriately before relying on a total.
Errors in source cells: A cell containing an error such as #N/A can prevent a normal SUM result. Find and handle the error according to the workbook’s intended rules rather than silently ignoring it.
Range ends too early: A fixed range such as B2:B100 excludes later records. Use a carefully defined dynamic range or an Excel Table if new rows are regularly added.
Unexpected zero: Check that the source range contains actual numeric values and that the code points to the intended workbook and worksheet.

When debugging, step through the procedure with F8 in the Visual Basic Editor and inspect the values of ws, the range address, and total. On Mac, the editor’s menu commands are available even if the function-key shortcut differs. Testing with a small, known set of values can also reveal whether the problem is the range, the data, or the expected result.

Choosing between VBA, a formula, and other tools

VBA is useful when summing is one step in a repeatable workflow—for example, calculating a total, placing it in a report, and continuing with other operations. It is not automatically the best option for a simple total. A worksheet SUM formula is easier to see and updates with its inputs, while a macro can automate work across several ranges or sheets.

For a large dataset, a PivotTable can summarize values by categories without writing a macro, and Excel Tables make expanding data ranges easier to manage. Power Query is useful for importing and transforming data before analysis, but it is not a direct replacement for a simple cell total. Choose the method that fits how the result will be refreshed, reviewed, and maintained by the people using the workbook.

Remember that VBA requires a macro-enabled workflow: save code in a macro-enabled workbook such as an .xlsm file, and only enable macros in files you trust. Excel for the web does not execute VBA. On Windows and Mac desktop versions, VBA is supported, though some features and integrations can differ by platform.

Frequently Asked Questions

How do I sum a range in VBA and show the answer?
Assign Application.WorksheetFunction.Sum(ws.Range("A1:A10")) to a variable, then display that variable with a message box or write it to a worksheet cell. Qualify the range with the correct worksheet.

Can I use SUM instead of WorksheetFunction.Sum?
Yes. Use Application.Sum to call Excel’s SUM function from VBA. Its Variant return can be useful when you need to test for an error result; WorksheetFunction.Sum is a straightforward choice when you expect a valid numeric result.

Does VBA SUM include blank cells or text?
Blank cells and text stored in cells in the referenced range are ignored by SUM. Numeric values are added, while an error value in the range can make the calculation return an error.

Why does my VBA range sum return zero?
Common causes include pointing at the wrong sheet or range, summing values stored as text, or calculating an empty range. Verify the worksheet reference and inspect a few source cells to confirm they contain numbers.

Can I sum only rows that match a label?
Yes. Call Application.WorksheetFunction.SumIf with a criteria range, a criterion such as North, and the corresponding sum range. Use SUMIFS when records must satisfy more than one condition.

Can I run a VBA sum macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro; a worksheet SUM formula can still calculate in the web app.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.