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

Question

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.

score 3 · Answer 1 · answered Jul 12 '21 at 12:21

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);
}

score 0 · Answer 2 · answered Jul 12 '21 at 07:22

0

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

answered Jul 12 '21 at 07:22

du7ri

67
1
10

There's also a chance that your original script is referencing two different tables. `workbook.getTable("Table1")` will get the table named "Table1" while `workbook.getTables()[0]` just gets the first table in the workbook. You should get the table from the workbook once and use that reference throughout the rest of your script (like in @user16187680's answer). – Alexander Jerabek Jul 14 '21 at 16:24

user16187680 · Answer 3 · 2023-04-21T16:30:55.973

EVALUATE
    SELECTCOLUMNS(
        KEEPFILTERS('Control_Book'),
        "''[MONTH]", 'Control_Book'[MONTH],
        "''[BANK_IND]", 'Control_Book'[BANK_IND],
        "''[BAL_IND]", 'Control_Book'[BAL_IND],
        "''[PRODUCT]", 'Control_Book'[PRODUCT],
        "''[ACCOUNTING_NET_LOSS_US]", 'Control_Book'[ACCOUNTING_NET_LOSS_US]
    )

ORDER BY
    ''[MONTH],
    ''[BANK_IND],
    ''[BAL_IND],
    ''[PRODUCT],
    ''[ACCOUNTING_NET_LOSS_US]

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

3 Answers3