How to Use an Excel VBA Macro to Subtotal Values Below the Active Cell in WPS Spreadsheets
Question details
The user needs a VBA macro that writes a SUBTOTAL formula into the active cell to sum the contiguous values below it, stopping at the next blank cell.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is working with a column of values and wants a macro that inserts a dynamic SUBTOTAL(9) formula at the active cell, summing the current range until the next blank row.
- Observed behavior
- The expected formula uses SUBTOTAL function number 9 for SUM and relies on the next blank cell to define the range.
Open the workbook in WPS Spreadsheets and make sure the VBA editor is available. Save a test copy as a macro-enabled workbook (.xlsm) before modifying the VBA module.
Insert the VBA Macro That Adds a SUBTOTAL Formula Below the Active Cell
Use this method when you want WPS Spreadsheets to insert a dynamic SUBTOTAL(9) formula in the selected cell and sum until the next blank cell.
The macro uses function number 9, which returns SUM and follows Excel-compatible SUBTOTAL behavior. The range is determined from the cell directly below the active cell down to the last contiguous value.
In WPS Spreadsheets, press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
Go to Insert > Module in the VBA editor to create a new code module.
Paste the following code into the module: Sub InsertSubTotalFormula() ActiveCell.Formula = "=SUBTOTAL(9," & ActiveCell.Offset(1).Address & ":" & ActiveCell.Offset(1).End(xlDown).Address & ")" End Sub
Close the editor, select the target cell above the values, then press Alt+F8, choose InsertSubTotalFormula, and click Run.
Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm) so the VBA macro is retained.
Manually Enter a SUBTOTAL Formula Without VBA
Use this alternative when you prefer not to run macros and only need a one-time subtotal.
Add a Dynamic SUBTOTAL Macro in WPS Spreadsheets
WPS Spreadsheets includes VBA macro support, so you can open the VBA editor, insert the SUBTOTAL macro, run it to sum values below the active cell until the next blank cell, and save the workbook as a macro-enabled Excel file.
- 1. Download and install WPS Office: Download WPS Office from the official site and install WPS Spreadsheets on your PC.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open the workbook that contains the values you want to subtotal.
- 3. Access the VBA editor: Press Alt+F11 to open the VBA editor, then insert a new module.
- 4. Paste and run the macro: Paste the InsertSubTotalFormula macro, select the target cell in the worksheet, and run the macro with Alt+F8.
- 5. Save your macro-enabled file: Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) to preserve the macro.
Frequently Asked Questions
Will this VBA macro run in WPS Spreadsheets?
Yes. WPS Spreadsheets supports VBA macros on Windows. Press Alt+F11 to open the editor, insert a module, paste the code, and save the workbook as .xlsm.
What does function number 9 do in SUBTOTAL?
Function number 9 tells SUBTOTAL to use SUM. It sums the selected range and follows standard SUBTOTAL behavior in Excel-compatible workbooks.
Why does the macro only sum until the next blank cell?
The code uses ActiveCell.Offset(1).End(xlDown).Address, which selects the contiguous range below the active cell until the first blank cell.
Can I change the macro to ignore hidden rows?
Yes. Replace 9 with 109 in the macro if you want the SUBTOTAL to ignore values in hidden rows.
Why should I save the workbook as .xlsm?
The .xlsm format is required to preserve VBA macros. If you save as .xlsx, the macro will be removed.
