Office Scripts - Clear Cell Button

[Jim_Currier__NPL]

Hi Guys,

Anyone any good with Office Scripts?

I want to add a 'Clear' button to the Excel web version.

Tried using similar script to the normal VBA but doesn't work.

If anyone knows what the Office Scripts version of the below is it would be appreciated.

Sub Clearcells()
'Updateby Extendoffice
Range("C2", "C8").ClearContents
End Sub

[#2]

Hi @Jim_Currier__NPL ,

Actually, it has a syntax of a VBA code, but I see that there is an error in that, apparently.

If you want to clear the whole range between rows 2 and 8 in column C, you may use this code:

Sub Clearcells()
'Updateby Extendoffice
Range("C2:C8").ClearContents
End Sub 

If you want to clear ONLY the cells C2 and C8, you may use this code:

Sub Clearcells()
'Updateby Extendoffice

Range("C2").ClearContents
Range("C8").ClearContents

End Sub

Let me know if it works for you.

[#3]

Also, the same solution can be applied in Office Scripts with the following pieces of code.

For the entire range:

workbook.getActiveWorksheet().getRange('C2:C8').clear()

For only cells C2 and C8:

    workbook.getActiveWorksheet().getRange('C2').clear()
    workbook.getActiveWorksheet().getRange('C8').clear()

@Jim_Currier__NPL

[#4]

Hi @rzaneti 

Still struggling for this to work, this is what i have entered;

Script seems to run with no errors, but cells do not clear.

Im all new to office scripts so bit of a learning curve.

Kind Regards,

[#5]

@Jim_Currier__NPL remove the "//" from your code and try to run it again. They are a comment in the code, so the Excel will ignore that line. 

[#6]

Hi @rzaneti,

That worked, however.................

I need the formulas to stay in place and just clear data entered in the cells.

That just cleared everything.

Sorry should have said at the start.

Kind Regards,

[#7 · Accepted solution]

Hi @Jim_Currier__NPL ,

If you want to keep the formats and just clear the values inside the cells, this is an alternative code:

  workbook.getActiveWorksheet().getRange('C2:C8').setValues([[""], [""],[""],[""],[""],[""],[""]])

Here we are just assigning "" to all cells in the range C2:C8. There are more effective ways to write this code if you need it for more than 7 cells. 

Please, test it and let me know if it works for you.

[#8]

Hi @rzaneti 

Spot on, that worked with no issues.

Thanks for your help.

Kind Regards,