Introduces how to clear cells, rows, and columns in Office scripts.
Operations
Using script recordings will target the active sheet.
However, that would change the behavior depending on where the script is run, so it is recommended to specify the sheet name as well.
Change the red text accordingly.
Cell range.clear(Clear apply to);Clear apply to
Specify any of the following.
| Clear apply to | Specify |
|---|---|
| Content | ExcelScript.ClearApplyTo.contents |
| Format | ExcelScript.ClearApplyTo.formats |
| Hyperlinks (content and format will remain) | ExcelScript.ClearApplyTo.hyperlinks |
| Cell hyperlinks and formatting (content, conditional formatting, and data validation will remain) | ExcelScript.ClearApplyTo.removeHyperlinks |
Cell clear
Specify by cell address
To clear cell A1 on the Test sheet, do the following
Specify the cell address with the getRange method.
// Clear cell A1. workbook.getWorksheet("Test").getRange("A1").clear(ExcelScript.ClearApplyTo.contents);
Specified by index (sequential number starting from 0)
To clear cell A1 on the Test sheet, do the following
Specify the index in the getCell method, where the first column (column A) and the first row are both 0.
Cell A1 becomes getCell(0,0).
// Clear cell A1.
workbook.getWorksheet("Test").getCell(0,0).clear(ExcelScript.ClearApplyTo.contents);
Row clear
To clear the first row of the Test sheet, do the following.
// Clear first row.
workbook.getWorksheet("Test").getRange("1:1").clear(ExcelScript.ClearApplyTo.contents);
Column clear
To clear the first column (column A) of the Test sheet, do the following.
workbook.getWorksheet("Test").getRange("A:A").clear(ExcelScript.ClearApplyTo.contents);
For those who want to learn Office script effectively
The information on this site is now available in an easy-to-read e-book format.
Or Kindle Unlimited (unlimited reading).
You willl discover how to about basic operations.
By the end of this book, you will be equipped with the knowledge you need to use Excel Office Script to streamline your workflow.