VBA & Macro Problems

Clear Excel Cells with an Office Script Without Removing Formatting

Jason MillerJason Miller Nov 18, 2026 870 views

Question details

Convert a VBA ClearContents macro into an Office Script that clears Excel cells without removing their formatting.

Clear Excel Cells with an Office Script Without Removing Formatting
Product
Microsoft Excel for the web, Office Scripts
Device & OS
not provided
Scenario
The user wants to clear a continuous range or selected individual cells in an Excel workbook using Office Scripts.
Observed behavior
The desired result is to remove cell values and formulas while retaining existing formatting.
Before you start

Open the workbook in Excel for the web and check that the Automate tab is available. Identify the worksheet and cell addresses you want to clear; the examples below use the active worksheet.

Solution 1Recommended

Clear a Continuous Range with Office Scripts

Use this approach to clear all values and formulas in a range while preserving cell formatting.

1
Create a script

In Excel for the web, open the workbook, select the worksheet you want to edit, and choose Automate > New Script.

2
Add the range command

Replace the editor contents with: function main(workbook: ExcelScript.Workbook) { workbook.getActiveWorksheet().getRange("C2:C8").clear(ExcelScript.ClearApplyTo.contents); }

3
Run the script

Choose Run in the script editor. Cells C2 through C8 will have their values and formulas cleared, but their formatting will remain.

Clear a Continuous Range with Office Scripts
Specify contents: Use ExcelScript.ClearApplyTo.contents rather than clear() without an argument. The contents option preserves formatting.
Free Microsoft Office alternative

Edit Excel Workbooks with WPS Spreadsheet

WPS Office is a free, lightweight, easy-to-use Microsoft Office alternative with a familiar interface and Excel format compatibility for a smoother move between office suites. Office Scripts are a Microsoft Excel feature, so use Excel to run the scripts above.

Edit spreadsheet cells and ranges in a familiar interfaceOpen and save Excel-compatible XLSX and XLS filesWork with spreadsheets in a free, lightweight office suiteMove between compatible Office documents with less disruption
microsoft office alternative - wps office

Frequently Asked Questions

Does clearing contents remove formulas as well as values?

Yes. Range.clear(ExcelScript.ClearApplyTo.contents) removes both values and formulas from the specified cells while preserving formatting.

Can I clear cells on a specific worksheet instead of the active one?

Yes. Replace workbook.getActiveWorksheet() with workbook.getWorksheet("Sheet1"), using the name of your target worksheet.

Why not use clear() without an argument?

Calling clear() without an argument clears more than contents, including formatting. Specify ExcelScript.ClearApplyTo.contents to retain formatting.

Can I run my VBA macro directly as an Office Script?

No. VBA and Office Scripts use different languages and APIs. Create a new script under Automate > New Script and replace VBA commands with their Office Scripts equivalents.