Yes. In Excel for Microsoft 365 on Windows, a VBA macro can remove duplicates with the built-in Range.RemoveDuplicates method. Specify the full range of records, the columns that define a duplicate, and whether the range has a header row; the macro then removes repeated records and keeps the first occurrence.
What an Excel duplicate-removal macro does
Excel’s duplicate-removal feature compares records using one or more selected columns. When it finds repeated records according to those columns, it removes the later occurrences from the specified range. It is not just a visual filter: the operation changes the data, and the deleted records are not recoverable with Ctrl+Z after a macro runs.
For example, imagine a list with columns for Customer ID, Name, Email, and Region. If Customer ID is the key, two rows with the same ID count as duplicates even if the other cells differ. If you choose Customer ID and Email together, only rows matching on both fields count as duplicates. The first matching record is retained, so the order of the data affects which version remains.
A macro is useful when the cleanup must be repeated, applied to a consistent layout, or triggered as part of a larger process. For a one-time cleanup, Excel’s Data tab command may be quicker.
Prepare the data before running the macro
Before automating removal, decide what “duplicate” means for the task. Identical-looking rows are not necessarily duplicate records, and repeated values in one column may be legitimate. A shared product category, for instance, is usually not enough to identify two sales transactions as duplicates.
- Choose the key column or combination of columns that uniquely identifies a record.
- Include every column belonging to each record in the range the macro processes. A range that excludes related fields can leave the worksheet’s data out of alignment.
- Confirm whether the first row contains headings. The code below assumes it does.
- Save a separate copy of the workbook or data before running the macro. VBA actions cannot be reversed with Ctrl+Z.
- Check whether blank keys, inconsistent spacing, or different spellings need cleaning first. A macro does not know that “North ” and “North” may be intended to mean the same thing.
It is also sensible to sort or otherwise arrange the records first if you want a particular version to survive. Since the first occurrence is kept, put the preferred row first—for example, the row with the latest update date, if that is the version your process calls authoritative.
Basic VBA macro for a four-column list
This example assumes row 1 contains headings, records are in columns A through D, and column A has a value in every data row. It identifies duplicates by the combined values in columns A and B. Change the range and key-column numbers to fit the actual worksheet.
Option Explicit
Sub RemoveDuplicateRows()
Dim ws As Worksheet
Dim lastRow As Long
Dim dataRange As Range
' Work on the active sheet; confirm it before running.
Set ws = ActiveSheet
' Assumes headers in row 1 and a value in column A for every record.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "No data rows found."
Exit Sub
End If
Set dataRange = ws.Range("A1:D" & lastRow)
' Compare columns A and B; keep the first matching record.
dataRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub
In the code, the numbers in Columns:=Array(1, 2) are positions inside dataRange, not worksheet column letters. Because the range starts at A, position 1 means A and position 2 means B. If the range instead started at C, position 1 would mean C. To compare only the first column of the range, use Columns:=1. To compare all four columns, use Columns:=Array(1, 2, 3, 4).
The last-row calculation looks up the last nonempty cell in column A. If column A can be blank in records near the bottom, this may produce a range that is too short. Use a column that is consistently populated, or revise the code to find the last used row across the full dataset. Also update both the range boundaries and key-column positions if the data is not in A:D.
How to add and run the macro in Excel
- Save a backup copy of the workbook. The macro removes data, and Ctrl+Z will not restore its changes.
- In desktop Excel for Windows, press Alt+F11 to open the Visual Basic Editor. If the Developer tab is visible, you can also select Developer > Visual Basic.
- In the editor, select Insert > Module, then paste the code into the new module.
- Return to Excel and make the worksheet containing the list the active sheet. Verify its columns match the assumptions in the macro.
- Run the procedure from Developer > Macros, select
RemoveDuplicateRows, and choose Run. You can also use Alt+F8 to open the macro list on Windows. - Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if you need to keep the VBA code.
Excel for Mac also supports VBA macros in the desktop application, though keyboard shortcuts can depend on the keyboard and function-key settings. Use Tools > Macro > Visual Basic Editor to open the editor, or try Option+F11 (on some keyboards, Fn may also be needed). Excel for the web does not run VBA macros; open the file in desktop Excel to execute this code.
Choosing the right duplicate keys
Key selection is the most important decision in a duplicate-removal macro. If the key is too broad, valid records may be deleted. If it is too narrow, repeated records may remain. For example, two orders from the same customer are not duplicates merely because the customer name matches. An order number may be a better key; for a dataset with no unique order number, a combination such as customer ID, order date, and product code might be more appropriate.
Use the columns that define record identity for the particular dataset, not automatically every column. Comparing every field removes only rows that match across all included columns. Comparing a single ID removes rows with repeated IDs even when other fields disagree, which can discard useful differences. If repeated IDs have conflicting details, inspect those records instead of assuming that the first one is always correct.
Excel’s built-in Remove Duplicates tool and VBA method are generally used to compare cell values, not formatting. If apparent duplicates remain, check for differences such as extra spaces, different identifiers, or values that look alike but are stored differently. Decide how to normalize those fields before removal, and preserve the original data while checking the result.
Headers, range boundaries, and tables
The sample uses Header:=xlYes, which tells Excel that the first row is a heading rather than a record to compare. If the range has no header row, change this to Header:=xlNo and set the range to start with the first actual record. The method also accepts xlGuess, but explicitly identifying the header status is clearer and avoids relying on Excel to infer it.
The range must cover the complete records being cleaned. In the example, the range is A:D, so make sure all fields for each record are within those columns. If the actual list runs from A to H, change the range to include H and keep the key numbers relative to its first column. Take particular care with neighboring data outside the list: inspect the result after running the macro to make sure unrelated columns have not been affected or separated from their records.
For a formatted Excel table, a table-aware macro can use the table’s data range rather than a fixed address. That can be more reliable when the table grows, but it requires naming the correct table and deciding how to handle the header. For a simple, stable worksheet layout, a clearly documented range is often easier to review.
Common problems and safer habits
- Nothing happens: Check that the correct worksheet is active, that the range contains at least two records, and that the macro’s range matches the data location.
- Some duplicates remain: Confirm that the intended key columns are included and that the values really match. Differences in whitespace or spelling can make two entries distinct.
- Good records disappear: The selected key may be too narrow. Restore from the backup and choose a more precise combination of columns before trying again.
- The bottom records are skipped: The macro finds the last row using column A. If that column has gaps, use a consistently populated anchor column or adjust the last-row logic.
- The macro is blocked: Make sure the file is in a macro-enabled format and only enable macros in workbooks from sources you trust. Do not enable unknown code simply to run a cleanup.
A careful workflow is to run the macro on a copy, compare the record count before and after, and review a few retained records against the original. For important data, keep a dated untouched export. Those steps make it easier to detect an incorrect key choice than relying on the macro to judge whether the remaining list is sensible.
Macro versus Excel’s built-in command
For a manual cleanup, select the list and choose Data > Remove Duplicates in Excel for Microsoft 365 on Windows. In the dialog, select or clear the columns that define a duplicate and confirm whether the data has headers. Excel reports how many duplicate values were removed and how many unique values remain. The command changes the data in place, so make a copy first if the original must be preserved.
A macro is preferable when the same cleanup needs to be repeated consistently or incorporated into a larger VBA routine. The manual command is often preferable when a person should review the key columns each time or when the task is occasional. Both approaches require a sound definition of duplicate records; automating an ambiguous rule only makes the mistake repeatable.
If you need to show unique records without deleting source rows, consider a separate output or a dynamic-array approach instead. The UNIQUE function is available in Microsoft 365 and Excel 2021 or later and returns unique values or rows in another area. Unlike Remove Duplicates, it leaves the source list intact and can update as source data changes. It is an alternative for viewing results, not a substitute when the goal is to permanently clean the original list.
Frequently Asked Questions
Does an Excel macro keep the first duplicate or the last one?
The RemoveDuplicates method keeps the first occurrence it encounters in the supplied range and removes later matching records. Arrange or sort the data first if a particular record should be retained.
Can I remove duplicates using only one column?
Yes. Set the key to the first column of the supplied range with Columns:=1. The entire matching record within the range is treated as a duplicate based on that key, so use a column that actually identifies records.
Can I remove duplicates using multiple columns?
Yes. Pass their relative positions as an array, such as Columns:=Array(1, 3) to compare the first and third columns of the range. A record is considered a duplicate when the chosen key fields match.
Can I undo a VBA duplicate-removal macro?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z will not reverse this operation. Save a separate copy or use a duplicate sheet before running the macro.
Will this macro run in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the VBA procedure.
Related
- How to Remove Group Rows in Excel
- How to Resolve a Corrupted Excel File Without Losing Data
- How to Quickly Group Rows in Excel with the Outline Tools
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Remove Item From Dictionary: Methods and ExamplesTo remove one entry from a VBA dictionary, call dict.Remove key, where key is the entry’s key—not its…
- Excel VBA: Copy and Paste Selected Cells with a MacroTo copy selected cells with VBA, call the range’s Copy method and specify a destination cell, as in…
- VBA Toggle: Make a Button Switch a Setting On or OffYes. You can turn a VBA macro into an on/off toggle by assigning it to a clickable worksheet…
- Access VBA: Open an Excel File and Work With Its DataYes. Access VBA can open an Excel workbook by controlling the Excel application, or it can import worksheet…
- Excel Macro File: How to Save, Open, and Use It SafelyAn Excel macro file is a workbook that can store VBA code to automate tasks; the usual format…
- Run Macro in Excel Not Working? Causes, Checks, and Fixes to TryIf a macro will not run in Excel, first check that you opened the file in the desktop…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.