Office Script "Delete All Rows Except The First Header Row" in Excel

user_15503773 0
Viewed 930

I Try to create a office script, that do the same as this excel makro:

Sub clear()

Rows("2:" & Rows.Count).ClearContents

End Sub

my office Script looks like this:

function main(workbook: ExcelScript.Workbook)
{


  let table = workbook.getTable("Table1");
  let rowCount = workbook.getTables()[0].getRowCount();

 
  table.deleteRowsAt(2, rowCount);

}

i get following error:

Line 9: Cannot read property 'deleteRowsAt' of undefined

i dont get why the property "deleteRowsAt" is undefined.

it needs to be a office script, because i want to automate a process with power automate.

office-scripts power-automate excel
2 Answers

Not sure why you didn't use table in your rowCount code. You don't know if you are even getting that table for sure.

Assuming your table starts in Row 2 and the headers are in Row 1, to delete the table, see the following code below.

function main(workbook: ExcelScript.Workbook) {
  let table = workbook.getTable("Table1");
  let rowCount = table.getRowCount();
  table.deleteRowsAt(0, rowCount);
}
user_161876800

the office Script works, u need to check if the Table name is really your table name

user_155037730
Related