🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Remove Duplicates in Excel — Dedupe in One Line (and Why It Deletes With No Undo)

|

VBA Remove Duplicates in Excel — Dedupe in One Line (and Why It Deletes With No Undo)

TL;DR — Range.RemoveDuplicates is the Data > Remove Duplicates button as one line of code. Two things make it dangerous in a way a loop is not. First, it is destructive: it deletes rows in place, keeps the first occurrence of each duplicate, and cannot be undone once a macro has run. Second, its Columns argument takes offsets inside the range, not sheet column numbers — Columns:=Array(1, 2) means the first and second columns of the range, so if your range starts at column C, that is C and D, not A and B. Always pass Header:=xlYes unless your range truly has no header, and back up the data before you run it.

' Remove rows that repeat the same Email (column 3 of this range). Keeps the first of each.
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Contacts")
ws.Range("A1").CurrentRegion.RemoveDuplicates _
        Columns:=3, _              ' 3rd column OF THE RANGE, counting from A1
        Header:=xlYes              ' the first row is headers, don't treat it as data

Deduplicating a list is one of the most common cleanup jobs there is, and Excel already has an engine for it — you do not need to loop, compare, and delete by hand. But RemoveDuplicates hides two sharp edges behind its convenience: it destroys data with no safety net, and its key argument does not mean what most people assume. This guide is built around those two facts.

What you'll learn

  • The mental model — it is the Remove Duplicates button, and it deletes in place
  • The rule that matters most — Columns numbers are range offsets, not sheet columns
  • Why Header:=xlYes is almost always required
  • That it keeps the first row, not the last — and there is no undo
  • One column or several: what actually counts as a "duplicate"
  • When to use RemoveDuplicates, AdvancedFilter, or a Dictionary instead

The mental model: it's the button, and it deletes

Range.RemoveDuplicates does exactly what Data > Remove Duplicates does in the ribbon: it scans the range, and for every row whose key columns repeat an earlier row, it deletes that row. The rows below slide up to fill the gap, just like any other row deletion.

Two consequences follow immediately, and both catch people out. It changes your data in place — there is no "copy the uniques somewhere else" mode; the original rows are gone. And after a macro runs, Excel's undo stack is empty, so neither you nor the user can press Ctrl+Z to get them back. Treat RemoveDuplicates the way you would treat a Delete: something you only run against data you have backed up, or against a copy you made on purpose.

The rule that matters most: Columns are range offsets, not sheet columns

This is the bug that makes RemoveDuplicates "delete the wrong rows." The Columns argument counts columns from the left edge of the range, starting at 1 — it has nothing to do with sheet column letters or their absolute numbers.

' Range starts at column C. We want to dedupe on the Email column, which is sheet column E.
' Email is the THIRD column of the range (C=1, D=2, E=3), so:
ws.Range("C1:F500").RemoveDuplicates Columns:=3, Header:=xlYes   ' correct - E

' WRONG - passing the sheet column number 5 points one past the range (or errors):
ws.Range("C1:F500").RemoveDuplicates Columns:=5, Header:=xlYes   ' not the Email column

If your range happens to start at column A, the offset equals the sheet column number and the bug hides — which is exactly why it surprises people the first time a range starts anywhere else. When you dedupe on more than one column, pass an array of range offsets: Columns:=Array(1, 3) means the 1st and 3rd columns of the range together form the key.

Header:=xlYes — or your header becomes data

RemoveDuplicates has to know whether the first row is a header or real data. The argument is Header, and if you omit it the default (xlNo) treats row 1 as data. That has two nasty effects: your header row gets compared against the data, and — if a data row happens to match the header text — it can even be deleted.

ws.Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes

Pass Header:=xlYes whenever your range includes a header row, which is almost always. The only time you use xlNo is a genuinely headerless block of values. Getting this wrong rarely raises an error; it just quietly mangles the top of your table.

It keeps the first, not the last — and there's no undo

When rows collide, RemoveDuplicates always keeps the first occurrence and deletes the later ones. Most of the time that is fine. But if your rows are in chronological order and you want the most recent record per key, "keep the first" is the opposite of what you need.

There is no option to flip this. The idiom is to sort first, then dedupe: sort so the row you want to survive is the topmost one for its key, and the "keep first" behaviour now keeps the right row. Want the latest entry per customer? Sort by date descending, then RemoveDuplicates on the customer key. (See VBA Sort for the Header:=xlYes and whole-record rules that keep a sort from scrambling your columns.)

And to say it once more, because it is the mistake that hurts: there is no undo after a macro. If the removed rows might ever matter, do not delete them — extract the uniques to a new location with AdvancedFilter instead, which leaves the original untouched.

One column or several: what counts as a duplicate

The Columns argument also defines what "duplicate" means. Pass one column and two rows are duplicates when that single field matches. Pass several and rows are duplicates only when all of the named columns match together.

' Duplicate = same Email (one-column key).
rng.RemoveDuplicates Columns:=3, Header:=xlYes

' Duplicate = same First AND Last name together (composite key).
rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

This is worth a moment's thought before you run it, because it decides which rows disappear. "Same person" might mean same email, or same name, or same name and birthdate — and each choice removes a different set of rows. The columns you name are the definition; everything else on the row just comes along with whichever copy survives.

RemoveDuplicates vs AdvancedFilter vs Dictionary

Three tools dedupe, and they are not interchangeable:

  • RemoveDuplicates — fastest to write, mutates in place, keeps the first, no undo. Right when you genuinely want to shrink the data itself and you have a backup.
  • AdvancedFilter — extracts a unique copy to a new location and leaves the source intact. Right when you need a distinct list without destroying the original, or when you want uniques filtered by criteria at the same time.
  • Dictionary — full control in code. Right when your dedupe key is computed (case-insensitive, trimmed, a combination), when you need to count occurrences as you go, or when you want to decide per row which copy to keep.

The judgment worth stating plainly: if there is any chance you will want the removed rows back, do not use RemoveDuplicates — its lack of undo is the whole reason AdvancedFilter exists.

How ExcelMaster helps

RemoveDuplicates looks like a one-liner and behaves like a loaded gun: the Columns offsets that are not sheet columns, the Header flag that silently eats your header row, the first-not-last rule that keeps the wrong record, and the missing undo that turns a mistake permanent. Every one of them deletes data without raising an error.

ExcelMaster lets you describe the cleanup instead. Say "remove duplicate contacts, keeping the most recent by date" or "dedupe on email but leave the original sheet alone," and it picks the right tool — sorting before a RemoveDuplicates when you want the latest, or an AdvancedFilter to a new range when the source must survive — and always backs up the sheet before deleting anything. You keep the workbook and the code; you skip the run where an off-by-a-column key quietly removed the wrong rows.

Frequently asked questions

How do I remove duplicates in VBA?

Call RemoveDuplicates on the range: ws.Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes. The Columns argument names which columns define a duplicate (as offsets inside the range), and Header:=xlYes tells Excel the first row is a header. It deletes matching rows in place, keeping the first of each, so back up your data first.

Why does VBA RemoveDuplicates delete the wrong rows?

Almost always because Columns counts from the start of the range, not from sheet column A. If your range begins at column C, Columns:=1 is column C, not A. Pass the offset of the key column within the range, and use Array(...) of offsets for a multi-column key.

Does VBA RemoveDuplicates keep the first or last duplicate?

It keeps the first occurrence and deletes later ones, and there is no option to change that. To keep the last (for example, the most recent record), sort the data so the row you want is on top for its key, then run RemoveDuplicates.

Can I undo RemoveDuplicates in VBA?

No. Running it from a macro clears Excel's undo stack, so neither Ctrl+Z nor Application.Undo will restore the deleted rows. Back the data up first, or use AdvancedFilter to copy the unique rows to a new location instead of deleting in place.

How do I remove duplicates on multiple columns in VBA?

Pass an array of range offsets to Columns: rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes. Rows are treated as duplicates only when all the named columns match. The numbers are positions within the range, counting from its first column as 1.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-10.

Related guides: VBA Advanced Filter · VBA Dictionary · VBA Sort · VBA Delete Rows · VBA Range