VBA & Macro Problems

How to Create a VBA Macro to Clear and Recalculate Totals in Excel

Tauseeq Magsi Oct 10, 2026 869 views

Question details

The user needs an Excel VBA macro that can dynamically locate a 'TOTAL' label in column H, clear any existing totals in columns K and L, and independently recalculate those columns, inserting a zero if a column is empty.

How to Create a VBA Macro to Clear and Recalculate Totals in Excel

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Automating the recalculation of changing column data dynamically without manual formula updates or interruption from system alerts.
Observed behavior: The current macro implementation only toggles a single column's total or fails to calculate independently, requiring a new script to handle dynamic row counts, multi-column clearance, and silent execution.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools in your spreadsheet ribbon settings.

Solution 1Recommended

Create and Run a Custom Recalculation Macro

Use this custom VBA script to locate the 'TOTAL' row in column H, clear the designated columns, and dynamically sum the values above without triggering error messages.

This solution utilizes the Find method to dynamically track your total row, meaning it will always work even if the number of data entries above it changes.

It also incorporates an empty-cell check to place a zero when no values exist, preventing formula errors.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor, or click on 'Visual Basic' located under the Developer tab.

Insert a New Module

In the VBA editor, right-click on your workbook in the Project pane on the left, navigate to 'Insert', and select 'Module' to create a blank scripting window.

Write the Recalculation Logic

Write a script that defines your worksheet and uses `Range("H:H").Find(What:="TOTAL")` to locate the target row. Assign the row number to a variable, then use `ClearContents` on columns K and L for that specific row.

Add Dynamic Summation and Zero Checks

For both column K and L, use an `If` statement checking `WorksheetFunction.CountA`. If it equals zero, set the cell value to 0. Otherwise, apply a SUM formula dynamically using `Range.Formula = "=SUM(K1:K" & row - 1 & ")"`. Include `Application.DisplayAlerts = False` to prevent popup messages.

Run the Macro

Close the VBA editor. Press ALT + F8 in your spreadsheet, select your newly created macro from the list, and click 'Run' to recalculate the totals automatically.

Create and Run a Custom Recalculation Macro

Tip: You can assign this macro to a custom button on your worksheet for one-click recalculations. Simply insert a Shape, right-click it, and choose 'Assign Macro'.

Solution 2

Alternative: Use Excel Subtotals or Table Features

If you want to avoid writing VBA scripts, you can format your dataset as a Table to manage dynamic totals automatically.

Advanced Macro Support

Easily Manage VBA Macros with WPS Spreadsheet

WPS Spreadsheet provides comprehensive native support for Excel VBA macros. You can create, edit, and deploy your custom automated recalculation scripts effortlessly using an intuitive interface.

1. Download WPS Office: Download and install WPS Office, ensuring that you select the version that includes built-in VBA support.
2. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing Macro-Enabled Workbook (.xlsm).
3. Enable the Developer Tab: Go to Options and ensure the Developer tools are visible on your ribbon to access your macro controls.
4. Run Your Script: Click 'Macros' under the Developer tab, select your custom total recalculation macro, and execute it seamlessly.

Native support for writing and running Excel VBA scriptsSeamless compatibility with Microsoft Excel .xlsm and .xlsb formatsFree and lightweight software with fast performanceFamiliar ribbon interface for effortless macro management

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro return an error when a column is empty?

If the data range above the total row is entirely empty, standard SUM formulas or basic VBA calculations might return errors. You must instruct the macro to check if the column has data using the CountA function and forcefully insert a zero if the count is zero.

How do I stop VBA from displaying warning messages during recalculation?

You can suppress standard system alerts and messages by inserting the command 'Application.DisplayAlerts = False' at the very beginning of your VBA script. Always remember to set it back to 'True' at the end of your script.

How can I automatically trigger this macro when data changes?

You can utilize the Worksheet_Change event in your specific sheet's VBA code module. By setting a condition to check if the modified cell falls within your target data columns, you can automatically call your recalculation macro without clicking any buttons.

Does WPS Spreadsheet support Excel VBA macros natively?

Yes, the VBA-enabled version of WPS Spreadsheet provides high compatibility with Microsoft Excel macros. You can open your .xlsm files and execute your existing VBA code without having to rewrite your scripts.

Excel & Data Problems