VBA & Macro Problems

How to Automatically Clear Excel Cells at a Scheduled Time

Ayan Masood Oct 9, 2026 869 views

Question details

The user needs to automatically clear specific cell ranges in an Excel workbook at 8:30 a.m. every day without losing the structural formatting of the cells.

How to Automatically Clear Excel Cells at a Scheduled Time

Product: Excel for the Web / Desktop Excel
Device & OS: not provided
Scenario: A team relies on a daily operational spreadsheet hosted in the cloud. They want to automate a daily reset of the data inputs at a specific time while preserving the workbook's formatting layout.
Observed behavior: The user is attempting to run a VBA macro in Excel for the web, which is not supported, preventing the scheduled automation from executing.

Before you start

Note that VBA macros cannot run in Excel for the web. To achieve scheduled automation in the cloud, you must use Power Automate and Office Scripts, or switch to a desktop application that supports VBA.

Solution 1Recommended

Use Power Automate and Office Scripts for Excel on the Web

Since VBA is not supported in the web version of Excel, you can use an Office Script triggered by a scheduled Power Automate cloud flow.

This cloud-based approach allows the automated task to run on schedule without requiring you to keep your computer turned on or the workbook open.

Create the Office Script

Open your workbook in Excel for the web. Navigate to the 'Automate' tab and click 'New Script'. Write a script using the clear method specifically targeting contents: workbook.getWorksheet("Sheet1").getRange("A1:B10").clear(ExcelScript.ClearApplyTo.contents). Save the script.

Create a Scheduled Flow in Power Automate

Log into Microsoft Power Automate. Click on 'Create' in the left menu and select 'Scheduled cloud flow'. Name your flow and set the starting date and trigger time (e.g., 8:30 a.m.). Set the repeat frequency to daily.

Add the Excel Online Action

Click 'New step' and search for 'Excel Online (Business)'. Select the 'Run script' action. Choose the SharePoint or OneDrive location where your file is stored, select your workbook, and choose the script you just created.

Save and Test

Save your Power Automate flow. You can use the 'Test' button in the upper right corner to run the flow manually and verify that the cell contents are cleared while the formatting remains intact.

Use Power Automate and Office Scripts for Excel on the Web

Formatting Preserved: By specifying ExcelScript.ClearApplyTo.contents in your script, all borders, background colors, and font styles will remain completely unaffected.

Solution 2

Use a VBA Macro with Application.OnTime in Desktop Excel

If you prefer using desktop applications, you can write a VBA macro that uses the ClearContents method and schedule it using the Application.OnTime function.

Automate Tasks with Macros in WPS Office

WPS Spreadsheet features robust, built-in VBA support, allowing you to seamlessly run macros and schedule automated tasks using the Application.OnTime function without needing an expensive subscription.

1. Open Your Workbook in WPS: Launch WPS Office, click on 'Open', and select your existing XLSX or XLSM file.
2. Access the VBA Editor: Navigate to the 'Tools' tab and click on 'Macro' > 'Visual Basic Editor', or simply press ALT + F11.
3. Write the Macro: Insert a module and write your VBA script using the Range("...").ClearContents command to ensure your formatting is preserved.
4. Set the Schedule: Use the ThisWorkbook module to add the Application.OnTime script, triggering your macro automatically at your designated time.

Fully compatible with Microsoft Excel XLSX, XLS, and XLSM macro-enabled formatsIntegrated VBA Editor to easily write, edit, and schedule advanced scriptsFully supports the ClearContents method to delete data while maintaining your cell formatsLightweight, fast, and free alternative for desktop spreadsheet automation

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro fail to run in Excel for the web?

Excel for the web is a browser-based application and does not support the execution of VBA macros. Any automation requiring VBA must be executed in a desktop spreadsheet application like Microsoft Excel Desktop or WPS Office.

How can I delete cell data without losing borders and background colors?

If you are using VBA, always use the ClearContents method (e.g., Range("A1:B10").ClearContents) instead of the generic Clear method. If you are using Office Scripts for the web, use the clear(ExcelScript.ClearApplyTo.contents) syntax. Both methods delete only the values or text while leaving the cell formatting completely untouched.

Does my workbook need to remain open for a scheduled VBA macro to trigger?

Yes. If you rely on the Application.OnTime method in VBA, the spreadsheet application must be running and the specific workbook must be open at the exact scheduled time. If you want the process to run in the background without keeping your computer on, you should use cloud automation like Power Automate.

Excel & Data Problems