To remove duplicate rows with Excel VBA, select the full table, including its header row, then run a macro that calls Range.RemoveDuplicates. For example, dataRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes compares the first two columns of the selected range and keeps the first matching row; the complete macro and setup steps follow.
VBA code for removing duplicate rows
This macro works on the range you select. It treats the first selected row as a header and compares columns 1 and 2 within that selection. If those two fields match an earlier row, Excel removes the later duplicate from the selected range.
Sub RemoveDuplicateRowsFromSelection()
Dim dataRange As Range
Set dataRange = Selection
' Avoid acting on a noncontiguous selection or a one-row range.
If dataRange.Areas.Count > 1 Then Exit Sub
If dataRange.Rows.Count < 2 Then Exit Sub
' Column numbers are relative to the selected range.
dataRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub
For instance, if the selected table is A1:D100, Array(1, 2) compares columns A and B, not B and C. The macro removes duplicate entries from the selected table while retaining the first occurrence. Select all columns belonging to each record, not just the columns used to identify duplicates: otherwise values in unselected columns may no longer stay aligned with the surviving records.
To compare one field, use this version instead. It assumes the first row of the selection is a header and compares only the first selected column.
Sub RemoveDuplicatesByFirstSelectedColumn()
Dim dataRange As Range
Set dataRange = Selection
' A single-column selection is valid; reject multiple separate areas.
If dataRange.Areas.Count > 1 Then Exit Sub
If dataRange.Rows.Count < 2 Then Exit Sub
dataRange.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub
How to add and run the macro
Use the desktop version of Excel for Microsoft 365 on Windows to create and run VBA macros. Excel for the web cannot create, edit, or run VBA macros. The macro itself also works in desktop Excel for Mac, though menu names and keyboard shortcuts can differ.
- Save a copy of the workbook before making changes. Macros cannot be undone with Ctrl+Z.
- Select the full, contiguous table range, including its header row. Make sure no unrelated columns are included.
- Open the VBA editor with Alt+F11 on Windows. On Mac, use Option+F11 if available, or choose Developer > Visual Basic.
- In the editor, choose Insert > Module, paste one of the code examples, and close or switch back to Excel.
- Run the macro from Developer > Macros, select its name, and choose Run. Alt+F8 opens the macro list on Windows; on Mac, use Developer > Macros if the shortcut differs on your keyboard.
- Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) if you need to keep the macro in the file.
If the Developer tab is not visible in Windows, enable it through File > Options > Customize Ribbon, then check Developer. On Mac, ribbon customization is available under Excel > Settings > Ribbon & Toolbar. Only enable macros in files you trust; do not lower Excel’s macro security settings just to run code from an unknown source.
What counts as a duplicate
A duplicate is defined by the columns named in the macro, not necessarily by every column in the table. If a customer table has Customer ID, Name, and Email, comparing only Customer ID removes repeated IDs even if a name or email differs. Comparing all three fields removes a row only when all three selected values match a previous record. Choose the comparison fields according to what makes a record unique in your data.
The column numbers in Columns:=Array(1, 2) are relative to the range passed to RemoveDuplicates. If the selected range begins at column C, number 1 means C, number 2 means D, and so on. The macro keeps the first match it encounters in the current row order; it does not sort records or decide which record is most recent. Sort by a date or priority first if a particular version of a repeated record should be retained.
For a range with no header row, change Header:=xlYes to Header:=xlNo. Do not use xlYes if the first data record is actually in the first row: Excel would treat that record as a header and leave it out of duplicate checking. These macros deliberately do not guess whether a header exists.
Prepare the data before removing duplicates
Review the table before running a deletion macro. A duplicate-removal operation changes data, and apparent matches can reflect either true repeated records or inconsistent data entry. Keep an untouched copy of the source sheet or workbook until you have verified the result.
- Check that the selection includes every field belonging to a record, including fields beyond the visible portion of the table.
- Decide which column or combination of columns forms a unique key. A person’s name alone, for example, may not uniquely identify a person.
- Look for blank rows, merged cells, totals, and notes inside the data block. Separate summaries from the records you intend to process.
- Sort first if you want to control which repeated record remains. The method keeps the first occurrence in the range’s existing order.
- Consider making a backup worksheet or using a copy of the file, especially when the source data is difficult to reproduce.
Formatting can make values look different without making the underlying values different. For example, a date displayed with different date formats can represent the same stored date. Conversely, values that look alike may differ because one is stored as text or contains leading or trailing spaces. If results are surprising, inspect the underlying values and clean inconsistent entries before rerunning the macro.
Choosing the right columns to compare
Use one comparison column when that field is a reliable unique identifier, such as a product code. Use multiple columns when uniqueness depends on a combination, such as invoice number plus line item, or last name plus postal code. Add columns to the array in the order they appear in the selected range. The order of the comparison columns does not change the basic rule: a later record is removed when the chosen fields match an earlier record.
For example, suppose a selected range contains ID, Region, and Status. With Columns:=Array(1), repeated IDs are treated as duplicates even if Region or Status differs. With Columns:=Array(1, 2), the same ID can remain more than once if its Region differs. With Columns:=Array(1, 2, 3), the method compares all three fields. Match the code to the business meaning of a duplicate rather than simply selecting every column by habit.
Common problems and how to fix them
If the macro removes too many rows, check the selected range and the comparison columns first. A common mistake is comparing only a broad field such as a surname or product description when a unique ID should also be included. Another is selecting a range that begins in a different column than expected and then treating the array numbers as worksheet column letters.
If the first record is missing from duplicate checks, confirm that the header setting is correct. Use xlYes only when the first row of the selected range contains headings, and use xlNo when it contains data. If the macro seems to do nothing, make sure you selected more than one row, selected a contiguous range, enabled macros for the trusted workbook, and ran the correct macro name.
Do not select only the key columns when the rest of the row contains associated information. The duplicate-removal method operates on the range supplied to it. Restricting that range can leave related data outside it untouched, so records in neighboring columns may become mismatched. Also check whether formulas, hidden rows, blank cells, or inconsistent text are affecting what you regard as a duplicate. A backup makes it possible to inspect the original rows and choose a better comparison key.
VBA versus Excel’s built-in Remove Duplicates command
For a one-time cleanup, the ribbon command may be simpler: select the table, then choose Data > Remove Duplicates. In the dialog, check or clear My data has headers and choose the columns Excel should compare. The command is useful when you want to inspect the settings before deleting anything and do not need to repeat the task.
VBA is helpful when the same cleanup must be repeated, when it belongs in a larger workbook process, or when you want a one-click operation with fixed comparison columns. The same caution applies to both methods: they remove data, and their outcome depends on the selected range, header setting, and comparison fields. Neither method should be treated as a substitute for defining what makes a record unique.
For a non-destructive review, use conditional formatting to highlight duplicates or create a separate list with Excel functions available in your version. Microsoft 365 and Excel 2021 support the UNIQUE function, which can return distinct values without deleting the source records. That approach is useful for inspection, but it is not identical to removing duplicate rows from a table with all associated fields.
Frequently Asked Questions
How do I remove duplicates in Excel VBA based on one column? Select the full table, including its header, and use dataRange.RemoveDuplicates Columns:=1, Header:=xlYes. The number 1 means the first column of the selected range. Include the full table in the range so that the rest of each record remains together.
How do I remove duplicates based on two columns? Use dataRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes. This compares the first and second columns of the selected range as a pair. Adjust the numbers to match the key columns’ positions inside that range.
Does VBA keep the first or last duplicate? The RemoveDuplicates method keeps the first matching record in the current range order and removes later matches. To keep the newest or highest-priority record, sort the data into the desired order before running the macro.
Can I undo a VBA duplicate-removal macro? No. Excel’s normal undo history does not restore changes made by a VBA macro, so Ctrl+Z will not reverse this deletion. Save a copy or duplicate the worksheet first, then verify the output before replacing the original data.
Will this VBA macro run in Excel for the web? No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the code; the web version can still be used for many ordinary spreadsheet tasks.
Related
- Password Protect an Excel Sheet as Read-Only
- Password Protect an Excel Sheet From Viewing: What Works
- Password Protect an Excel Sheet from Editing Safely
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Index: Access Items by PositionA VBA Scripting.Dictionary has no built-in numeric index for its entries: you retrieve an item by its unique…
- Excel VBA Dir Function: Find Files and FoldersIn Excel VBA, the Dir function finds a file or folder that matches a path and optional wildcard…
- VBA Delete First Row: Code and Safe OptionsTo delete row 1 of a worksheet with VBA, use ws.Rows(1).Delete, where ws is the worksheet you intend…
- VBA Find Sheet Names in a Workbook: Practical MethodsTo find worksheet names with VBA, loop through the workbook’s Worksheets collection and read each sheet’s Name property.…
- VBA Dictionary Item: Get, Set, Check, and Loop ThroughA VBA Dictionary Item is the value associated with a key in a Dictionary object. In Excel for…
- VBA Find the Maximum Value in an Excel Column: How-ToTo find the largest numeric value in an Excel column with VBA, use the worksheet function Max on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.