How to delete rows using Office Scripts based on dynamic column range in Excel?

Question

I am working on a task in Excel using Office Scripts and I'm facing an issue with deleting rows based on a dynamic column range. Here's what I'm trying to achieve:

I want to delete entire rows below the used range in column A. The column range is dynamic and is determined by the data in column A.

Ex: Below excel after row no 10 there is no data in A column so what I want is to delete rows after 10th row. Column range is dynamic.

Output I'm expecting;

Answer 1

Please try.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let table = selectedSheet.getTables()[0];
    if (table) {
        let lastCell: ExcelScript.Range = table.getRange().getColumn(0).getLastCell();
        if(! lastCell.getText()){
            let lastRow = lastCell.getRangeEdge(ExcelScript.KeyboardDirection.up);
            selectedSheet.getRange(`${lastRow.getRowIndex() + 2}:${lastCell.getRowIndex() + 1}`)
                .delete(ExcelScript.DeleteShiftDirection.up);
        }
    }
}

Microsoft documentation:

ExcelScript.Range interface