Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Office Scripts - Clear Cell Button

Copper Contributor

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

7 Replies

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.

  

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

Hi @rzaneti 

 

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

Jim_Currier__NPL_0-1675933948172.png

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,

 

@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. 

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,

best response confirmed by Jim_Currier__NPL (Copper Contributor)
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.

Hi @rzaneti 

 

Spot on, that worked with no issues.

Thanks for your help.

 

 

Kind Regards,

1 best response

Accepted Solutions
best response confirmed by Jim_Currier__NPL (Copper Contributor)
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.

View solution in original post