Calculation Issues

How to Sum Values Until the Next Blank Row in Excel

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs to calculate subtotals for continuous blocks of data separated by blank rows or specific demarcating labels (such as "Assembly").

Product: Excel
Device & OS: not provided
Scenario: Automating the process of grouping data and summing line totals between blank rows or specific text labels as new data is continuously added.
Observed behavior: Users require a dynamic method to calculate totals for segmented data blocks without manually updating formulas every time a blank row or custom label is encountered.

Before you start

Before applying VBA or Python scripts, ensure your data structure is consistent. Remove any accidental empty rows within your actual data blocks to avoid premature subtotals.

Solution 1Recommended

Use a VBA Macro to Calculate Subtotals

A VBA macro is highly effective for automatically detecting specific rows (like 'Assembly' or blanks) and calculating the sum for each continuous block of data.

VBA (Visual Basic for Applications) allows you to loop through your entire dataset, evaluate each row, and automatically insert sum formulas exactly where they are needed.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor in your workbook.

Insert a New Module

Click on Insert in the top menu and select Module to create a blank script window.

Paste and Run the Code

Paste your macro code that loops through your data range (e.g., A1:D39), checks for 'ASSEMBLY' labels or empty cells, and calculates line totals. Press F5 to execute the macro.

Solution 2

Use Python in Excel for Data Grouping

If you are using Microsoft 365, Python in Excel allows you to load ranges into a DataFrame and programmatically sum values between specific rows.

WPS Spreadsheet Tip

Easily Sum Data Until Blank Rows in WPS Spreadsheet

WPS Spreadsheet provides powerful built-in tools like 'Go To Special' and AutoSum, allowing you to instantly calculate subtotals for multiple data blocks without writing complex VBA code.

1. Select Your Data Range: Highlight the entire column of data, including the numerical values and the blank rows where you want the subtotals to appear.
2. Open Go To Special: Press Ctrl + G to open the 'Go To' dialog box, then click the 'Special' button.
3. Highlight Blank Cells: Choose the 'Blanks' option and click 'OK'. This will select only the empty cells below each data block.
4. Apply AutoSum: Press the AutoSum keyboard shortcut Alt + =. WPS Spreadsheet will automatically sum the values directly above each blank cell.

100% compatible with Microsoft Excel formulas and macros (.xlsx, .xlsm)No coding required for basic subtotal calculationsFamiliar user interface for seamless migrationLightweight, fast, and free to use

microsoft office alternative - wps office

Frequently Asked Questions

Can I sum values until a blank row without using VBA?

Yes, you can highlight the data column, use the 'Go To Special' feature to select all blank cells, and then press the AutoSum shortcut (Alt + =) to calculate subtotals instantly.

Why does Python in Excel require an internet connection?

Python in Excel processes your code and data using Microsoft Cloud services rather than executing it locally on your machine, making a stable internet connection mandatory.

Are VBA macros fully supported in WPS Office?

Yes, WPS Office supports VBA natively. You can seamlessly run and edit standard Excel scripts (.xlsm files) to automate subtotal calculations and other repetitive tasks.

Excel & Data Problems