Forum Discussion

Jhowe82's avatar
Jhowe82
Copper Contributor
May 16, 2024

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

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

 

 

Excel for web
Macros and VBA
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 16, 2024

    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

Resources