In this article, I show you different scripts to count the number of empty cells, to find the first or the last blank row including the row ID, to delete entire row for empty cell, etc.

 

When I use the script ?

When I have to deal with blank cells to do different actions.

 

How to create the script ?

Read How to create, edit and select an Office Script in an excel report

 

How to create the button to associate it with the script ?

Read How to create a button and associated it to an Office Script in an excel report

 

How is/are the script(s) ?

Copy the code below and paste it into your script. You will see my comments in green if exist so follow the help to adapt to your need.

To count empty rows and find first/last empty row ID:

function main(workbook: ExcelScript.Workbook) {
    // change Sheet1 by yours
    let sheet = workbook.getWorksheet('Sheet1');
    // change D2:D10 by yours
    let range = sheet.getRange("D2:D10").getValues();
    let emptycellcount = 0;
    let emptyrowid = 0;
    for (let i = 0; i < range.length; i++) {
        // alternatively, !range[i][0] instead of range[i][0] === null || range[i][0] === ""
        if (range[i][0] === null || range[i][0] === "") {
            emptycellcount++;
        }
    }
    // for last row id, (let i = range.length - 1; i >= 0; i--) instead of (let i = 0; i < range.length; i++)
    for (let i = 0; i < range.length; i++) {
        if (range[i][0] === null || range[i][0] === "") {
            // change 2 (D2) by your row number
            emptyrowid = i + 2;
            break;
        }
    } 
    console.log(emptycellcount);
    console.log(emptyrowid);
}              
              

To delete empty rows:

function main(workbook: ExcelScript.Workbook) {
    // change Sheet1 by yours
    let sheet = workbook.getWorksheet('Sheet1');
    // change D2:D10 by yours
    let range = sheet.getRange("D2:D10").getValues();
    for (let i = range.length - 1; i >= 0; i--) {
        if (!range[i][0]) {
            // change D and 2 (D2) by yours
            sheet.getRange(`D${i + 2}`).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
        }
    }
}              
              

To count empty rows and find first/last empty row ID in a table:

function main(workbook: ExcelScript.Workbook) {
    // change Table1 by yours
    let table = workbook.getTable("Table1");
    let range = table.getRange().getValues();
    let emptycellcount = 0;
    let emptyrowid = 0;
    for (let i = 0; i < range.length; i++) {
        // change 3 (column D) by yours knowing that column A = 0
        if (!range[i][3]) {
            emptycellcount++;
        }
    }
    // for last row id, (let i = range.length - 1; i >= 0; i--) instead of (let i = 0; i < range.length; i++)
    for (let i = 0; i < range.length; i++) {
        if (!range[i][3]) {
            // change 1 (D2-1) by your row number without header
            emptyrowid = i + 1;
            break;
        }
    }
    console.log(emptycellcount)
    console.log(emptyrowid)
}              
              

To delete empty rows in a table:

function main(workbook: ExcelScript.Workbook) {
    // change Table1 by yours
    let table = workbook.getTable("Table1");
    let range = table.getRange().getValues();
    for (let i = range.length - 1; i >= 0; i--) {
        // change 3 (column D) by yours knowing that column A = 0
        if (!range[i][3]) {
            table.getRange().getRow(i).delete(ExcelScript.DeleteShiftDirection.up);
        }
    }
}