Excel Online...How to clear multiple cells across multiple tabs

Copper Contributor

I have been searching but unable to find an answer or solution

 

I am new to all this Macro and Scripting stuff in Excel.  But I have a Workbook that I was able to create a Macro to clear multiple cells across multiple sheets.  All proud until I put in on our sharepoint just to find Excel Online doesn't support macros.

 

Below is what I have going.  Is there a way to be able to clear this data using the book in excel online.  I have seen references to Automate and Inserting buttons but I am not finding anything on my end.  Any help would be greatly appreciated.

 

 

Sub Button1_Click()
response = MsgBox("Are You Sure you want to clear data?", vbYesNo)
 
If response = vbNo Then
    MsgBox ("Macro Ending")
    Exit Sub
End If

   Sheets("AUS").Range("B11:I16,B19:I24,B27:I32,B3:I8").ClearContents
   Sheets("DAL").Range("B11:I16,B19:I24,B27:I32,B3:I8").ClearContents
   Sheets("HOU").Range("B11:I16,B19:I24,B27:I32,B3:I8").ClearContents
   Sheets("NOR").Range("B11:I16,B19:I24,B27:I32,B3:I8").ClearContents
   Sheets("OKC").Range("B11:I16,B19:I24,B27:I32,B3:I8").ClearContents
   Sheets("PHX").Range("B11:I16,B19:I24,B27:I32,B3:I8").ClearContents
   Sheets("SAN").Range("B11:I16,B19:I24,B27:I32,B3:I8").ClearContents
   Sheets("STL").Range("B11:I16,B19:I24,B27:I32,B3:I8").ClearContents
   Sheets("WAC").Range("B11:I16,B19:I24,B27:I32,B3:I8").ClearContents

End Sub

 

 

1 Reply

@Jhowe82 

For the Automate the script could be like

function main(workbook: ExcelScript.Workbook) {

  const sheets = workbook
    .getWorksheets()
  const names = ["AUS", "DAL", "HOU"] //etc
  const range = "B11:I16, B19:I24, B27:I32, B3:I8"

  for( let sheet of sheets) {
    if( names.includes( sheet.getName() ) ) {
      sheet
        .getRanges(range)
        .getAreas()
        .map( a=>a.clear(ExcelScript.ClearApplyTo.all) )
    }
  }
}

You may add the button which runs the script, with that script could be run by any user who has rights on workbook.

The only there is no interactivity, i.e. you can't ask for the confirmation like "Are you sure?"

Documentation is here Office Scripts documentation - Office Scripts | Microsoft Learn, details are if expand API reference->ExcelScript, like ExcelScript.Range interface - Office Scripts | Microsoft Learn