About This Article
This article is created using an automated generation workflow utilizing generative AI. We reviewed the Microsoft Learn Office Scripts Table API and script input parameter specifications, and revised the sample to make it harder to accidentally modify existing data.Verification Status: 📘 Official specifications confirmed, sample implemented, actual device verification in the target Microsoft 365 environment not yet performed
Bulk Delete Empty Rows in Excel — Safely Cleaning Only the Target Table with Office Scripts
If you want to automatically delete empty rows in Excel, targeting only the specific Excel table rather than the entire worksheet reduces accidents. Furthermore, in a workbook with multiple tables, it is safer to require the target name to be specified rather than automatically selecting the “first table”.
“Empty Rows” to Be Deleted This Time
In this sample, for a single row in the table, if all cells treated as strings are empty strings or spaces only, the row is deleted.
Numbers
0are not deletedIf even one string exists, the row is not deleted
If the formula result is an empty string
"", it is treated as empty in this judgment
If you strictly want to keep rows containing formulas themselves, you must change to a different rule that checks for the presence of formulas rather than values.
Do Not Guess the Target Table
Office Scripts allows defining additional parameters in the main function. This time, it accepts tableName?: string, and if there are multiple tables in the workbook, specifying the target name is required.
/**
* @param tableName Target table name. Can be omitted if there is only one table.
*/
function main(workbook: ExcelScript.Workbook, tableName?: string) {
const tables = workbook.getTables();
if (tables.length === 0) {
throw new Error("There are no Excel tables in the workbook.");
}
let table: ExcelScript.Table;
if (tableName && tableName.trim() !== "") {
const found = workbook.getTable(tableName);
if (!found) {
throw new Error(`The specified table "${tableName}" was not found.`);
}
table = found;
} else {
if (tables.length > 1) {
throw new Error("There are multiple tables. Please specify tableName.");
}
table = tables[0];
}
const values = table.getRangeBetweenHeaderAndTotal().getValues();
for (let row = values.length - 1; row >= 0; row--) {
const isBlank = values[row].every(value => String(value).trim() === "");
if (isBlank) {
table.deleteRowsAt(row, 1);
}
}
}
Why Delete from the Bottom Up?
If you delete rows from the top, the positions of subsequent rows shift immediately at that moment. Because the row numbers of unverified rows change, it causes skipped rows during loop processing. By proceeding backwards from the last row to row 0, the row numbers of unprocessed rows do not change.
flowchart TD
A[対象テーブルを決定] --> B[データ行を取得]
B --> C[最後の行から確認]
C --> D{全セルが空白?}
D -- はい --> E[そのテーブル行だけ削除]
D -- いいえ --> F[残す]
E --> G[1つ上の行へ]
F --> G
G --> C
“Safe” Does Not Mean Not Deleting
This code actually deletes table rows. What makes it safe is that it limits the modification scope inside the table and stops if the target is ambiguous. Operations such as backing up before execution or testing on a copy for important workbooks are still separately necessary.
Also, deleteRowsAt in Office Scripts is an API for deleting data rows in a table. Consider this separately from deleting rows across the entire worksheet.
