How to create clear form button via office script for web excel

Question

I've created a macro to clear a form and add the current date for the desktop app which is as follows:

Sub CommandButton1_Click()
    Range("E3").MergeArea.Clearcontents
    Range("E4").MergeArea.Clearcontents
    Range("E5").MergeArea.Clearcontents
    Range("E6").MergeArea.Clearcontents
    Range("E7").MergeArea.Clearcontents
    Range("A9").MergeArea.Clearcontents
    Range("A10").MergeArea.Clearcontents
    Range("A11").MergeArea.Clearcontents
    Range("E9").MergeArea.Clearcontents
    Range("E10").MergeArea.Clearcontents
    Range("E11").MergeArea.Clearcontents
    Range("G9").MergeArea.Clearcontents
    Range("G10").MergeArea.Clearcontents
    Range("G11").MergeArea.Clearcontents
    Range("B13").MergeArea.Clearcontents
    Range("G16").MergeArea.Clearcontents
    Range("G17").MergeArea.Clearcontents
    Range("A19").MergeArea.Clearcontents
    Range("A27").MergeArea.Clearcontents
    Range("A32").MergeArea.Clearcontents
    Range("B13") = Now
End Sub

I didn't realize that the web app does not support macro scripts so now I need to write a new code. However, because the document contains merged cells, a simple "clear cell" script is not working. Any suggestions on how to get around this?

Answer 1

Hi Cam,

Thanks for your question! Are you using Office Scripts? If so, in order to clear the contents of a merged cell, you need to specify the range address for the entire merged range - for example, if you've merged A1:B2 into a single cell, you need to reference it using that address (instead of just A1). Like this:

function main(workbook: ExcelScript.Workbook) {
    let worksheet = workbook.getActiveWorksheet();
    worksheet.getRange('A1:B2').clear(ExcelScript.ClearApplyTo.contents);
    // TODO: clear other cells
    worksheet.getRange('B13').setValue(new Date().toDateString());
}

Let me know if that helps or if you have any questions!

Answer 2

Hi,

1. First, you'll have to Unmerge Cells as Merged Cells cannot be directly cleared in the web app.
2. Here's a JavaScript code snippet that clears the contents of specified cells. You can add this code to a script editor in your Office 365 Excel workbook:

// Specify the cell addresses you want to clear var cellAddressesToClear = ["E3", "E4", "E5", "E6", "E7", "A9", "A10", "A11", "E9", "E10", "E11", "G9", "G10", "G11", "B13", "G16", "G17", "A19", "A27", "A32"];  // Clear the contents of the specified cells for (var i = 0; i < cellAddressesToClear.length; i++) {     Excel.run(function (context) {         var sheet = context.workbook.worksheets.getActiveWorksheet();         var range = sheet.getRange(cellAddressesToClear[i]);         range.clear(Excel.ClearApplyTo.contents);         return context.sync();     }).catch(function (error) {         console.log(error);     }); } 

To use this code, open Excel in Office 365, go to "Script Lab" or "Script Editor," paste the code, and run it. Make sure your Excel file is stored in OneDrive or SharePoint to work with Office 365 scripting.

Best Regards.