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;

taller · Accepted Answer · 2023-11-15 16:43:13Z

2

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

answered Nov 15, 2023 at 16:43

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related