VBA & Macro Problems

Excel Office Scripts: Delete Table Rows Based on Cell Values

Bushra ParveenBushra Parveen Nov 12, 2026 869 views

Question details

The user needs to delete Excel table rows when any cell in a row matches a specified value.

Product
Microsoft Excel Office Scripts
Device & OS
not provided
Scenario
An Office Script must inspect every row in Table1 and remove rows containing a target value such as "a".
Observed behavior
Deleting rows while iterating from top to bottom can shift row indexes and cause rows to be skipped or processed incorrectly.
Before you start

Confirm the table name in Excel, such as Table1, and set the target value exactly as it appears in the table. Make sure the script is intended to check every column between the table header and total rows.

Solution 1Recommended

Use a reverse loop to delete matching Excel table rows safely

Use this approach when the target value may appear in any cell and you need to prevent row-index shifts after deletions.

The script reads the table values first, then checks rows from the bottom upward. Removing a lower row does not change the indexes of rows that have not yet been checked.

String conversion makes the comparison consistent for text, numbers, and other cell values. Change the target variable to the value you want to remove.

1
Open the Office Script editor

In Excel for the web, open the workbook, select the Automate tab, and choose New Script to open the Office Scripts code editor.

2
Replace the generated code

Paste the following script into the editor, then change Table1 or the target value "a" if needed: function main(workbook: ExcelScript.Workbook) { const table = workbook.getTable("Table1"); const range = table.getRangeBetweenHeaderAndTotal(); const values = range.getValues(); const target = "a"; for (let i = values.length - 1; i >= 0; i--) { if (values[i].some(cell => String(cell) === target)) { table.deleteRowsAt(i, 1); } } }

3
Run the script

Select Run in the Office Scripts editor. Excel checks each row from the last data row to the first and deletes any row containing the target value.

Why the reverse loop matters: Deleting from the bottom upward keeps the indexes of unprocessed rows stable, so matching rows are not skipped.
Free Microsoft Office alternative

Move spreadsheet cleanup tasks to WPS Spreadsheets

WPS Office is a free, lightweight Microsoft Office alternative for opening, editing, and cleaning spreadsheet files. Its familiar spreadsheet interface supports seamless migration of common Excel workbooks while Office Scripts-specific automation remains an Excel feature.

  1. 1. Install WPS Office: Download and open WPS Office, then launch WPS Spreadsheets from the home screen.
  2. 2. Open the Excel workbook: Select Open from the WPS home screen and choose the XLSX workbook containing the table.
  3. 3. Review and clean the table: Use the spreadsheet filters or select matching rows directly in WPS Spreadsheets, then delete the selected rows from the worksheet.
Open and edit Excel workbooks with a familiar spreadsheet interface.Work with Microsoft Excel XLSX files and preserve common spreadsheet formatting.Use a lightweight application for reviewing, filtering, and manually removing table rows.Move common spreadsheet workflows to WPS without changing your everyday document formats.
microsoft office alternative - wps office

Frequently Asked Questions

Why should the Office Script delete rows from the bottom upward?

Deleting a row changes the indexes of rows below it. A reverse loop starts at the last row, so deleting a row does not alter the indexes of rows that still need to be checked.

How do I change the table used by the script?

Replace "Table1" in workbook.getTable("Table1") with the exact table name shown under Table Design or the table properties in Excel.

Does the script delete a row when the target appears in any column?

Yes. The values[i].some(...) expression checks every cell in the row and deletes the row when any cell converts to the target string.

How can I match values in only one column?

Use the column-specific version and set columnIndex to the zero-based position of the table column. A value of 0 refers to the first table column.

What happens if the table has no data rows?

The range returns no data rows, so the reverse loop performs no deletions. The table header remains intact.