Excel VBA Remove Duplicates: Methods and Safe Code

To remove duplicate rows with Excel VBA, call the range’s RemoveDuplicates method and specify which columns define a duplicate. For example, a macro can check a table in A1:D100 for repeated IDs in column A, keep the first matching row, and remove the later ones; make a copy of your workbook before running it because VBA changes cannot be undone with Ctrl+Z.

What Excel VBA RemoveDuplicates does

Range.RemoveDuplicates removes repeated records from a range in the worksheet. You choose the range to examine, then identify one or more columns whose values determine whether two rows count as duplicates. Excel keeps the first occurrence in the range and removes later occurrences that match on the selected columns. The method changes the range in place; it does not create a separate cleaned copy.

The rows are compared as records, not as isolated cells. If the selected key is column A, two rows with the same value in A are treated as duplicates even if their values in B, C, or D differ. That makes the choice of key important: a customer ID might be a suitable key, while a surname alone usually is not. You can also combine columns so that only rows matching on every chosen key field are considered duplicates.

The method’s basic form is someRange.RemoveDuplicates Columns:=Array(1), Header:=xlYes. The numbers in Columns are positions within the range supplied to the method, not necessarily worksheet column numbers. Header tells Excel whether the first row of that range is a header.

A basic macro for removing duplicate IDs

This example assumes the worksheet named Data has headers in row 1, IDs in column A, and records spanning columns A through D. It identifies the last used row by looking down column A, then removes later rows with IDs already seen. The macro does nothing if there is no data row beneath the header.

Sub RemoveDuplicateIDs()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range

    ' Use the worksheet that contains the records.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last nonempty cell in the ID column.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Stop if the range contains only its header or is empty.
    If lastRow < 2 Then Exit Sub

    ' Include every column that belongs to each record.
    Set dataRange = ws.Range("A1:D" & lastRow)

    ' Compare IDs in the first column of dataRange; keep the first match.
    dataRange.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

The macro uses Array(1) because column A is the first column of the A:D range. If the range began at C1 instead, its first range column would be worksheet column C, and the relevant position would still be 1. This distinction prevents a common error when the table starts somewhere other than column A.

How to run the macro safely

VBA runs in the desktop versions of Excel, not in Excel for the web. In Excel for Microsoft 365 on Windows, save a working copy of the workbook first, then press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the procedure into the module, and change the worksheet name or range assumptions if your data differs. Return to Excel, press Alt+F8, select RemoveDuplicateIDs, and choose Run.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the code. If Excel displays a security warning, only enable macros for a workbook whose source you trust. Excel for Mac also supports VBA in the desktop app, though the editor and shortcut behavior can differ by keyboard; Option+F11, sometimes with the Fn key, opens the editor on many Mac keyboards. Excel for the web can open an .xlsm file, but it cannot run its VBA macros.

Make a copy before running the macro. Excel’s normal Undo command does not reverse VBA changes.
Confirm the worksheet name, header row, key columns, and full width of each record.
Run the macro on a small sample copy first if you are unsure how the selected columns define a duplicate.
After it runs, check both the remaining records and the rows immediately below the cleaned range.

Choosing the range, headers, and key columns

The range should cover the header and all rows in the table, as well as every column that belongs to each record. If you select only A:A while the related information is in B:D, Excel may compact values in the selected range without treating the other fields as part of the same record. To avoid mismatched data, use a complete rectangular table range such as A1:D500, not just the column containing the duplicate key.

Set Header:=xlYes when the first row in the supplied range contains labels such as ID, Name, or Date. Set Header:=xlNo when the first row is a real record. You can use xlGuess, but an explicit setting is more predictable in a repeatable macro: the guess can depend on what Excel finds in the range. If the headers are on row 3, start the range at row 3 so that the header is still its first row.

For a composite key, include the range-relative positions of each field in the Columns array. Suppose the data range is A1:D500, and a record is unique only when both its ID in A and its date in C match. The positions are 1 and 3, so the method call is:

dataRange.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes

With this setting, repeated IDs on different dates remain, and repeated rows with the same ID and date are reduced to the first occurrence. If the range instead begins in column B, the worksheet’s column C is position 2 in that range. Work out positions from the left edge of the actual range, rather than copying worksheet column letters into the array.

Finding the last row without skipping records

The sample macro finds the last used cell in column A. That is efficient when every genuine record has an ID in A. If some records have a blank ID but contain other values, the last-row calculation can stop too early and leave those lower rows outside the range. Conversely, stray content far below the table in column A can make the range much larger than expected.

Choose a column that is reliably populated for every record, or determine the table boundaries in a way that matches the workbook’s layout. For a properly formatted Excel Table, using its table range can be easier to maintain as rows are added, although the code must refer to the actual table and worksheet names. Avoid assuming that CurrentRegion is always the right answer: a blank row or blank column can split a block of data and cause it to identify only part of the intended table.

Also consider what a blank key means in your data. If several rows have no ID, Excel may treat those rows as duplicates when the ID column alone is the selected key. Do not run a key-only cleanup if blank IDs represent separate records that must be preserved. Decide whether to fill, exclude, or separately review missing keys before removing duplicates.

Using the built-in Remove Duplicates command instead

For a one-time cleanup, the Excel interface may be simpler than VBA. Select a cell in the table, then choose Data > Remove Duplicates. In the dialog, confirm whether the data has headers, select the columns that define duplicate records, and choose OK. Excel reports how many duplicate values it removed and how many unique values remain.

The interface and VBA method use the same key idea: selected columns determine which records match, and the first occurrence is retained. The interface is useful when a person should inspect the options each time. VBA is useful for a repeated workflow, such as cleaning a monthly import with consistent headers and rules. Neither approach automatically knows what your business considers a duplicate; that decision belongs in the chosen columns and data rules.

For a non-destructive result, consider copying the data to another worksheet before cleaning or using a formula-based result where available. In Microsoft 365 or Excel 2021, the UNIQUE function can return distinct rows or values without deleting the source data, but it is not a VBA command and has different behavior and setup. A formula result can be easier to review, while RemoveDuplicates is a direct edit to the original range.

Common problems and how to avoid them

Wrong column positions: The numbers in Columns:=Array(...) are relative to the range, not worksheet column letters. Recalculate them if the range’s left edge changes.
Wrong header setting: If a real record is treated as a header, or a header is treated as data, results can be incorrect. Set xlYes or xlNo explicitly.
Only part of the record is selected: Include all related fields so rows remain coherent. A key-only range is not a safe substitute for a whole-record range.
Unexpected values look identical: Leading or trailing spaces, inconsistent spelling, and values stored differently can make records appear alike to a person but not match as expected. Clean or standardize the key fields first if that is part of the intended rule.
The worksheet name does not exist: A misspelled sheet name causes a VBA error. Match the tab name exactly, including spaces.
The macro skips the bottom of the data: The example relies on column A being populated. Pick a dependable key column or revise the last-row logic for your layout.

Duplicate removal is not the same as deleting visually repeated formatting, blank rows, or duplicate formulas. The method evaluates values in the specified columns; it does not decide that two entire records are duplicates based on font, color, or appearance. If your rule depends on normalized text, dates at a particular precision, or multiple fields, prepare those fields or select the appropriate composite key before calling the method.

When to keep a reviewable copy

Because the operation edits the range directly, a cleanup macro should fit the workbook’s risk level. For routine imports, preserve the untouched source on a separate sheet or save a dated copy, then clean a working table. For high-impact records, first generate a list of duplicate keys or run the macro on a disposable copy and inspect the removed cases. Remember that “first occurrence” means first in the order of rows at the time the method runs; sorting the table first can change which version is retained.

If matching rows contain conflicting details, removing later copies may discard information that matters. For example, two rows can share a customer ID but carry different contact details. In that case, decide whether to retain the newest record, combine fields, or flag conflicts for review. RemoveDuplicates is a row-removal tool, not a merge or reconciliation system.

Frequently Asked Questions

Does Excel VBA RemoveDuplicates keep the first row?
Yes. For each set of rows matching on the selected columns, it retains the first occurrence in the supplied range and removes later matches. If a different version should be kept, sort the data appropriately before running the method, and preserve a backup.

How do I remove duplicates based on two columns?
Include both range-relative column positions in the array. For a range A1:D100 where columns A and C form the key, use Columns:=Array(1, 3). The rows must match on both selected columns to count as duplicates.

Can I remove duplicates without a header row?
Yes. Supply a range that starts with the first data row and use Header:=xlNo. Avoid xlGuess when the presence of a header is known.

Can I undo a VBA duplicate removal with Ctrl+Z?
No. Excel clears its usual Undo history when a macro changes the workbook. Save a separate copy or test the macro on copied data before running it on the original records.

Can I run a VBA macro in Excel for the web?
No. Excel for the web can display and edit many workbooks, but it does not run VBA macros. Use desktop Excel for Windows or Mac to execute this procedure.

Does the macro remove duplicates across the whole worksheet?
No. It only processes the range passed to RemoveDuplicates. Confirm that the range covers all intended records and fields; data outside that range is not part of the comparison.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.