VBA & Macro Problems

Sum Selected Excel Cells with VBA Without a Formula

Huda QurayshiHuda Qurayshi Nov 13, 2026 868 views

Question details

Calculate the total of selected Excel cells, including nonadjacent areas, and write it to the active cell without a worksheet formula.

Sum Selected Excel Cells with VBA Without a Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to select one or more ranges, choose a destination cell, and insert their total as a value.
Observed behavior
The desired result is a numeric total in the destination cell rather than a SUM formula.
Before you start

Open the workbook in a desktop version of Excel that supports VBA and save it as an Excel Macro-Enabled Workbook (.xlsm). Enable macros only if you trust the workbook and its code.

Solution 1Recommended

Use two VBA macros to sum the selection and write the result

Store the selected range, then select a destination cell and sum every selected area into it.

The first macro stores a reference to the selection. The second macro sums each area and writes the result as a value; it does not copy the result to the clipboard.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic. In the editor, select Insert > Module.

2
Add the macros

Paste this code into the module: Option Explicit Private MyRange As Range Sub PrepareSum() If TypeName(Selection) <> "Range" Then MsgBox "Select the cells to sum first." Exit Sub End If Set MyRange = Selection End Sub Sub FinishSum() Dim Area As Range Dim Result As Double If MyRange Is Nothing Then MsgBox "Run PrepareSum after selecting the cells." Exit Sub End If For Each Area In MyRange.Areas Result = Result + WorksheetFunction.Sum(Area) Next Area ActiveCell.Value = Result End Sub

3
Store the selected cells

Return to the worksheet and select the cells to sum. To include nonadjacent areas, hold Ctrl while selecting them. Open Developer > Macros, select PrepareSum, and choose Run.

4
Write the total

Select the destination cell, then open Developer > Macros, select FinishSum, and choose Run. The cell receives the total as a value.

Use two VBA macros to sum the selection and write the result
Choose the destination carefully: FinishSum replaces the active cell's existing contents. The stored selection is temporary, so run PrepareSum again after closing the workbook or resetting VBA.
Sum selected cells in WPS

Write a selected-cell total with WPS Spreadsheets VBA

In a desktop edition of WPS Spreadsheets with VBA support installed and enabled, use the same two macros to total selected areas and write the result as a value.

  1. 1. Check VBA support: Open the workbook in WPS Spreadsheets and check that your desktop edition has VBA support installed and enabled.
  2. 2. Add the code: Open Developer > Visual Basic, insert a module, and paste the PrepareSum and FinishSum code from the solution above.
  3. 3. Select the source cells: Select the ranges to total, then use Developer > Macros to run PrepareSum.
  4. 4. Insert the result: Select the destination cell and run FinishSum from Developer > Macros. Save the workbook in .xlsm format to retain the macros.
Sum nonadjacent selected areas without leaving a formula in the destination cell.Work with Microsoft Excel .xlsx and .xlsm files; running VBA requires a supported WPS edition and VBA component.Use a familiar spreadsheet interface to select cells and manage macros.
microsoft office alternative - wps office

Frequently Asked Questions

Can the macro sum nonadjacent ranges?

Yes. Hold Ctrl while selecting the ranges, then run PrepareSum. FinishSum loops through the stored selection's Areas and adds each area's total.

Does the result contain a SUM formula?

No. FinishSum assigns the calculated total to ActiveCell.Value, so the destination contains a value rather than a worksheet formula.

Why does the macro ask me to run PrepareSum first?

The stored range is not available. Select the source cells and run PrepareSum from Developer > Macros before selecting a destination and running FinishSum.

Will the destination cell's contents be overwritten?

Yes. FinishSum replaces the contents of the active cell, so select an appropriate destination before running it.

How do I keep the macros in the workbook?

Use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm). You will still need to run PrepareSum again after reopening the workbook.