VBA & Macro Problems

Remove Duplicates and Blank Rows from an Excel Table with Office Scripts

Camila MilosovichCamila Milosovich Nov 18, 2026 869 views

Question details

Use an Office Script to sort an Excel table, remove duplicate records, and delete completely blank rows.

Remove Duplicates and Blank Rows from an Excel Table with Office Scripts
Product
Microsoft Excel Office Scripts
Device & OS
not provided
Scenario
Clean table records using table-based references instead of fixed worksheet ranges.
Observed behavior
The desired result is a sorted table without duplicate records or completely blank data rows.
Before you start

Make sure your data is formatted as an Excel table and check its name on the Table Design tab. Decide which columns define a duplicate; column indexes in the script start at 0.

Solution 1Recommended

Clean an Excel table with an Office Script

Use this script to sort a named table, remove duplicates based on selected columns, and delete rows whose displayed values are all blank.

The example sorts and checks duplicates using the first table column. Change the zero-based indexes to match your data. Removing duplicates keeps the first matching record in the sorted order, so confirm the sort order and duplicate columns before running the script.

The script treats cells containing only whitespace as blank. It temporarily hides the totals row so that it is excluded from duplicate removal.

1
Open the script editor

In Excel, open the workbook, select the Automate tab, and choose New Script.

2
Add the table-cleaning script

Replace the editor contents with the following script: function main(workbook: ExcelScript.Workbook) { const table = workbook.getTable("Table1"); if (!table) { throw new Error("Table1 was not found. Check the table name."); } const hadTotals = table.getShowTotals(); if (hadTotals) table.setShowTotals(false); try { if (table.getRowCount() === 0) return; table.getSort().apply([{ key: 0, ascending: true }]); table.getRange().removeDuplicates([0], true); if (table.getRowCount() === 0) return; const values = table.getRangeBetweenHeaderAndTotal().getValues(); for (let i = values.length - 1; i >= 0; i--) { const isBlank = values[i].every(value => String(value).trim() === ""); if (isBlank) table.deleteRowsAt(i, 1); } } finally { if (hadTotals) table.setShowTotals(true); } }

3
Set the table and columns

Replace Table1 with your table name. Change the sort key and the indexes in removeDuplicates([0], true) as needed; for example, use [0, 1] to compare the first two columns together.

4
Run and review

Select Run in the script editor, then review the sorted table to confirm that the intended duplicates and blank rows were removed.

Clean an Excel table with an Office Script
Check the duplicate key: A row with a blank value in the selected key column can still contain data elsewhere. It may be removed as a duplicate if another row has the same key, even though the row is not completely blank.
Free Microsoft Office alternative

Clean spreadsheet data with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files to sort data and remove duplicates in WPS Spreadsheet. Office Scripts are a Microsoft Excel feature and are not fixed or run by this alternative.

Sort spreadsheet records and remove duplicates through the interfaceCompatible with Microsoft Excel XLSX and XLS filesFamiliar layout makes migration from Office easierFree and lightweight for everyday spreadsheet work
microsoft office alternative - wps office

Frequently Asked Questions

How do I find my Excel table name?

Click a cell in the table and look for the table name on the Table Design tab. Use that name in workbook.getTable("Table1").

How do I remove duplicates based on more than one column?

Pass the zero-based indexes of the columns to removeDuplicates. For example, removeDuplicates([0, 1], true) compares the first and second columns together; true indicates that the range includes a header.

Does the script delete rows with just one empty cell?

No. Its blank-row check deletes a data row only when every returned cell value is empty or contains only whitespace.

Why does the script delete blank rows from the bottom up?

Deleting a row shifts the rows below it. Iterating backward with table.deleteRowsAt(i, 1) prevents those shifts from causing rows to be skipped.

What happens to the table's totals row?

The script temporarily hides the totals row before removing duplicates and restores it afterward if it was originally visible.