Sum Selected Excel Cells with VBA Without a Formula
Question details
Calculate the total of selected Excel cells, including nonadjacent areas, and write it to the active cell without a worksheet 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.
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.
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.
In Excel, open the Developer tab and select Visual Basic. In the editor, select Insert > Module.
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
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.
Select the destination cell, then open Developer > Macros, select FinishSum, and choose Run. The cell receives the total as a value.

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. Check VBA support: Open the workbook in WPS Spreadsheets and check that your desktop edition has VBA support installed and enabled.
- 2. Add the code: Open Developer > Visual Basic, insert a module, and paste the PrepareSum and FinishSum code from the solution above.
- 3. Select the source cells: Select the ranges to total, then use Developer > Macros to run PrepareSum.
- 4. Insert the result: Select the destination cell and run FinishSum from Developer > Macros. Save the workbook in .xlsm format to retain the macros.

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.




