Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Delete Both Duplicate Rows In Excel

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Basically I have two huge lists and need to do what the title of the thread says.

The first list is from about 3yrs ago and is very large

The second list is from recently and is about half the size.

I need to find all records on the SECOND list that are NOT on the first list (new records that have been added since the first list was generated). I do not care about records on the first list that are not contained in the second list.

I was thinking that if there is a way to delete both of the duplicate records, I would be left with only records that are only included on one list. Before the formula/macro was executed I would color code each list and simply remove all of the records from the original list, leaving me with only records contained in the second list but not the first.

I found this macro on excel and it isn't doing what I need it to do...


Sub DelDups()
'Determine the number of items in column B
 lastRow = Cells(Rows.Count, 2).End(xlUp).Row
On Error GoTo Done
'Loop through rows, starting at the bottom
  For delRow = lastRow To 1 Step -1
'If duplicates are found, delete both rows
  If Cells(delRow, 1) = Cells(delRow - 1, 1) Then
    Cells(delRow, 1).EntireRow.Delete
    Cells(delRow - 1, 1).EntireRow.Delete
   End If
End Sub

I ran that macro and it is not deleting records that appear in both lists.

And yes, before anyone asks, I made sure the data I wanted to filter was in column b.

View Answers     

Similar Excel Tutorials

Delete All Rows that Contain a Specific Value in Excel
Quickly find all rows in Excel that contain a certain value and then delete those rows. This is a simple technique ...
How to Add, Remove, and Rearrange Columns and Rows in Excel
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...
Delete All Empty Rows or Blank Cells from a Range in Excel
How to quickly delete all empty cells or rows from a range in Excel.  This allows you to quickly clean your data to ...
Prevent Duplicate Values in Excel
I show you how to prevent duplicate values being entered into Excel using Data Validation. Let's say we have a lis ...

Helpful Excel Macros

Filter Data in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
- This free Excel macro filters data to display only those records or rows that contain a value between two values. For e
Delete Duplicate Rows
- This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will delete
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Filter Data to Show Only the Bottom 10 Items in Excel - AutoFilter
- This Excel macro filters a selection of data in order to display only the bottom 10 items in the list or data set. This
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose

Similar Topics

Hi all

So I've gone through a sheet of records for a mailing list, deleting about 5k records from a list of 15k. Now I've got the original list of 15k, and a 'cleaned' list with 10k names. What I want is a list of the 5k names I deleted.

I've tried copying the 15k list and the 10k list onto the same spreadsheet, so now roughly 20k of those 25k records are duplicates, and the remaining 5k are the ones I want. Now I want to remove the duplicate records as well as the originals that were duplicated.

I thought I was onto something with the Advanced Filter button, which has a checkbox called "unique records only". But apparently that also leaves one of the two duplicate records on display, just as the "remove duplicates" button does.

Any suggestions?

Edit: BTW, the COUNTIF function apparently only applies to cells, not entire rows. And I need the entire ROW to be matching (I have records where the name cell is identical, but address cells aren't, etc, and those need to be treated as entirely different records).

I have a list with 55,000 rows (records).

A few hundred of these records are exact duplicates.

I need them removed.

When I use the 'Unique Records Only' check box on Advanced Filter, it doesn't appear to do a thorough job of removing all the duplicates.

Does anyone know of a relatively fast way to remove duplicate records, or maybe have some code that will do it?


Hi Guys

I hope someone can help with my VBA knowledge. The code below is meant to take a variable "txtEnquiryNo", find out which row contains this value on sheet "Project List", Copy the row to the next available row on sheet "LOG" -prior to deleting it from "Project List". Where I currently have Rows("191") & Rows("6") I need variables that refer to "delrow" & "activerows" respectively. Using variable names in the code gives me an error so this is probably not the best way to proceed.

Any help really appreciated.


Sub DeleteLog()
If txtEnquiryNo "" Or txtProspectNo "" Then
ActiveWorkbook.Sheets("Project List").Activate
actualrows = 3
delrow = 3
While Cells(actualrows, 1) ""
actualrows = actualrows + 1
Dim Seq As String
For i = 3 To actualrows
Seq = Cells(i, 22)
If Seq = txtEnquiryNo.Value Then
delrow = i
End If
End If
' Code to copy to Log sheet and delete from Project List
actualrows = 4
While Cells(actualrows, 1) "" Or Cells(actualrows, 2) "" _
Or Cells(actualrows, 3) "" Or Cells(actualrows, 4) "" _
Or Cells(actualrows, 5) "" Or Cells(actualrows, 6) ""
actualrows = actualrows + 1
End Sub

RESOLVED: Solution = get someone elses more powerful computer to do it.

Hi Guys,

I've tried searching around and I'm possibly not using the right search criteria but haven't found what I need yet.

Basically, I have a WB that has a list of emails in Column A Sheet 1 of about 4000 records, I then have a list of emails in Column B Sheet 2 of about 11000 records.

I need a macro to delete the duplicates out of Sheet 2 if they've already appeared in Sheet 1.

At the moment I'm currently using this


Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count
' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

Which has previously worked fine for lists of about 20 or so, but now if I up the values to my large amount of rows it crashes my computer.

Can anyone help??



I was wondering if somebody could offer me some help with the issue below. I am sure it could be done easily using the unique filter, but my brain can't work out how!

I have 2 lists of ID numbers, List A consists of 500 records and is in range A1:A500. List B consists of 196 records and is in range B1:B196.

The 196 records in list B also exist in list A. These are the important ones - and the ones which I need to keep. I am looking for a way to identify the remaining 304 records in list A and delete them, keeping only the original 196.

I hope this is a reasonalbly clear explanation - any help would be appreciated


I have a worksheet for which I filter to find empty records (rows).
This worksheet is volatile ... it will always have a variable number of records, and thus a variable number of empty records (rows).
In my current macro, if applies the filter presenting only those empty records, however, the rows are sporadic ... ie 12, 14, 100, 132, 377, 401. As it is now I simply highlight these rows and delete to get rid of them.
But, since these rows are volatile, the macro will only always delete 12:401, whether empty or not.

Here is my current code:

'Delete empty records (0011)
Selection.AutoFilter Field:=9, Criteria1:="0011"

How do I delete rows from a filtered list in a variable environment. In all cases, I will only ever want to delete the first "x" number of rows from the filtered data?



I have records in a database (list b in sample spreadsheet), I'd like to add unique records (list A), while eliminating any records that are in list b already. So this is not a straight forward eliminating duplicates.

The resulting list should be only records from List A that are not found in List B.

Thanks for the help.

I have a dataset of approx 260K+ records; I wish to delete records from this dataset based on my criteria range(a range of 25 items). With smaller datasets I would normally just apply the criteria to the dataset and then delete the resulting records. However, my criteria return approximately 30-35K records in this larger dataset. When I select these and attempt to "Delete Row", I get a message that the range is too large or too complex and excel cannot delete the selected rows.

I considered criteria to identify records I want to keep, but I'm dealing with medications delivered in a hospital setting and the list could literally be hundreds of items long and could miss new meds added to the formulary that I'm not aware of. Just much more efficient to identify the items that don't belong on the list than those that do.

Any alternative approaches to this problem would be appreciated.

I am trying to put this in VBA once a strategy for identifying and deleting the records is identified.

Thanks, CC

Hi All,

I've searched the forum and could not find a way to delete duplicate records greater than 100 records in each instance. I have over 330,000 records in Excel 2007 and the majority of duplicate records over 100 records but I only want to keep the first 100 dup records in each unique value instance.

Not sure if this is even possible, where most users want to see the single record whereas I'm looking to see the first 100 items of each.

Data sample:
Type Symbol Date
c lbu 01/01/10
c lbu 02/01/10
c lbu 03/01/10
x mmn 06/01/10
x mmn 08/01/10
etc... 300,000 plus records

Thanks a bunch for all the help!

Hi. I have a problem I'm trying to solve. I am merging two lists. Both are lists of names. There are some people on both lists. I want to eliminate the records of the people who were only listed in one of the original two lists and be left only with the list of names who are common to both lists.

Each person on both lists has an ID number that I can use to compare. But while Excel seems to make it easy to eliminate duplicates, I want to do the opposite. I want to eliminate the records of people who are not on both lists and keep only the diplicate records in the spreadsheet.

The only way I've been able to figure out how to do this is to go through manually, mark a column for the duplicates, then sort on that column to collect all the duplicate names and delete the rest. This is very time consuming with a list that contains thousands of names.

Does anyone have a method to identify and segregate the duplicates in a spreadhseet so I can delete the rest?



im using windows 7, office 2007

records= the name of the sheet were all my data is on (for list populating and storing data from the user)

F= userform name

SOFTWARELIST = a listbox populated with the data (S3:S10) from "records" (using additem)

i have written the following code that has an if statment.
for some unknown reason to me, the statement does not work, even thought all the condition are there to trigger it.

the code should:
get the value of the listbox (SOFTWARELIST) ,which the user selected from the list, then go to sheet "records" and "look" for the same value in a cell - from a range (which MUST be there, because is appeared/added to that list), and delete that cell, and the next cell to the left, and finaly, delete that value from the list.


Private Sub CommandButton9_Click()

S = Sheets("records").Range("S1").End(xlDown).Row

For Each cell In Sheets("records").Range("S3:S" & S)
If cell.Value = F.SOFTWARELIST.Value Then
    CR = cell.Row
    Sheets("records").Cells(CR, 19).Delete Shift:=xlUp
    Sheets("records").Cells(CR, 20).Delete Shift:=xlUp
    ind = Me.SOFTWARELIST.ListIndex
    Me.SOFTWARELIST.RemoveItem (ind)
    End If
Next cell

End Sub

i have no idea why it won't work.

it's driving me crasy...!!

thank you.

Hi everyone! I can't figure out how to entirely remove duplicate records from a list. I do not just want to delete duplicates - that's easy. Pretend that I have two lists - the first is 1 2 3 4 5 6 7 8 9, and the second is 3 6 9. I need a function that will return 1 2 4 5 7 8 after the list is filtered. I can't figure out how to do this at all and I can't find it anywhere on the internet. Any help is greatly appreciated. Thank you!


Got a question about a macro which deletes duble records and blanks rows in a sheet. I got the following macro, but it wont work.


Application.Calculation = xlManual
On Error Resume Next
Rij = ActiveCell.Row
kolom = ActiveCell.Column
Do While Cells(Rij, kolom)  ""
If Trim(Cells(Rij, kolom).Value) = Trim(Cells(Rij + 1, kolom)) Then
Selection.Delete Shift:=xlUp
Rij = Rij + 1
End If
Cells(1, kolom).Select
Application.Calculation = xlAutomatic

My records are all in column A with blank rows in between it and there are some records that are more times in the list. When i select cell A1 and run the macro, nothing happens...

Does somebody knowes why or knowes another macro that will delete dubble records and blank rows?

Kind regards,


I'm struggling with a simple task - trying to filter a list of records according to whether one of the colums of the record appears in a list.

Basically I have a spreadsheet with a number of records with the following columns: BP code; BP name; contact name; email address etc.

Then I have created a second list of the BP codes I am interested in and want to filter the records to get to only the ones which have BP code which appears on the second list.

Hope that makes sense. I have attached a cut down spreadsheet with sensitive data removed and would be grateful for any help with this.

Edit: Meant to add I highlighted the records in yellow that I want to end up with - records with BP Code CAQU002 should not be in results because that BP code is not in second list..

I am novice to Excel and VBA.

I need to be able to compare Sheet1-Column A to Sheet2-Column A
for duplicates and then delete the ENTIRE row in Sheet2 of the duplicates.
There may be more than one incidence of the duplicate in
Sheet2-Column A
I need to keep the header intact too.

I am looking for any macro solution.

This link below of Sample 2 almost works for my needs except
it does not go beyond row 100 and it does not delete the entire row.
It also deletes the header and doesn't delete the second incidence of the

How do I modify this macro to delete the ENTIRE row
and to go beyond the A100 row?
The total number of rows can vary in Sheet1 and Sheet2.

Here is the code:


Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
   ' Loop through all records in the second list.
   For iCtr = 1 To iListCount
      ' Do comparison of next record.
      ' To specify a different column, change 1 to the column number.
      If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
         ' If match is true then delete row.
         Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
         ' Increment counter to account for deleted row.
         iCtr = iCtr + 1
      End If
   Next iCtr
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

There are plenty of threads like this already, and I've been reading them trying to piece together some code that will work, but having problems. Here is what I have so far:

Private Sub Worksheet_Deactivate()
LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For Ndx = LastRow To 1 Step -1
   If Cells(Ndx, 1 = "Nil") Then
   End If
   Next Ndx
End Sub

What I'm trying to do is to delete invalid rows when switching away from the Records worksheet. The only possible invalid row will be the last row with some data autopopulated by another macro. I've a formula in column A starting in cell 2 that checks if the row if invalid, if so a value of "Nil" is assigned, otherwise it's the row # -1.

I'll post back if I manage to solve it before anyone has a chance to weigh in.

Hello - I am tying myself in knots. There are a couple of things that I am trying to do.

1) If I have two lists in excel - I want to identify/highlight common records between the two.
2) I would like to delete records from list 1 if they occur in list 2.

Can anyone help??



Ok, have another one for the gurus.

I have a list of names. (Let's say B1:B10) I have four cells picking random names from that list. (Let's say A1:A4) So, I want A1 to pick from all of the names, then A2 to pick from all of them, except I want the name A1 picked to be excluded. So if there were 7 names originally, A2 would only have 6 to choose from, and ect ect for A3 and A4.

Basically I just want to ensure that all four cells pick unique names from the list.

(The word unique just made me think that there might be an excel function for this but I'm going to post the thread anyway...)


Alright, kinda figured out, but still want to know if there is an easier way.

This is what I did.

I made a new column beside my column of names. Let's call it B1:B10. In B1 I put

=randbetween(1,Num in list)

In B2 I put


=MOD(B1+ INT(RAND() * (Num in list - 1) ), Num in list ) + 1

I know how it works, but I can't seem to make it work for 4(or 5 or 6) picks. Here's where I found the solution.

Found it online. Seems to work ok for 2 picks. Gotta be a better way though.

Ok I figured out the formula, and I don't believe it can be adapted for 4 picks. So what I did was make a column of random numbers up to however many names were in the list. So if 7 names my random nums went from 1 to 7. Then I did an if statement that said "if the first number in this column is not equal to either of my first two picks, then it's the third pick, and if not, then it goes to the second number in the list of random numbers." I realize this is limited, but with enough of them, it'll be nearly impossible to get to the point where it doesn't pick a good number. Ugh it's ugly.

I need help with a macro that will look at a smaller list of customer names and VIN numbers and delete all of the duplicates off of another master list with all customers on it. I would like to be able to select the column that i want to sort--for example delete by VIN or name or address..etc. Microsoft has one that is similar but is not easy to use with over 10,000 names. Below is the code they provided. I have two sheets and i am trying to compile them into one sheet with no duplicate VINS and names. Any ideas would be great!

The following sample macro compares one (master) list against another list, and deletes duplicate items in the second list that are also in the master list. The first list is on Sheet1 in the range A1:A10. The second list is on Sheet2 in the range A1:A100. To use the macro, select either sheet, and then run the macro. Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

I have downloaded a report that is messy and needs to be cleaned up showing only unique records. Instead of manually going through and deleting each duplicate record or row I am wondering if there is a simpler way to accomplish this. In my list I have 10 column headings and column heading labeled ACCT would be the column I would want to verify if the rows below contain the same number, if true, then delete these rows. The list is already sorted by account number.

Hi folks

I know everyone says this but I'm a bit of a newb with Access and the small amount of VBA I understand is self taught.

I have a data entry form that just has one field to enter a loginID. I want it to list on the same form all loginIDs that have been entered so that it's easy to see at a glance which have already been added. I've managed to get it do do this using a list box, is this the best/only way? It needs to be able to update as soon as a new record is saved.

So then, my next problem is how can I delete from that list, so that it comes off the list box and also from the table it's coming from (tblusers)?

I want a delete button that when pressed, deletes which ever loginID is selected in the list box. Any ideas please?

Many thanks


Does anyone know if there is a way to delete duplicate values in list of names. I have a spreadsheet with about 10 000 records but each person is reprasented several times in the list. I would like to simply delete any duplicates, but going through the list deleting row by row is just driving me mad. :-)

Hello -

I have looked around this site and found some examples of how to write this macro but unfortunately none of them work for me.

I have a list of data (this list is variable) and I want to delete the rows that have the word Revaluation in column L.

How do I do this?

I tried the following but it did not work

Sub Delete()

Dim Lastrow As Long, i As Long
Lastrow = Cells(Rows.Count, "L").End(xlUp).Row
For i = Lastrow To 1 Step -1
If Range("L" & i).Value = Revaluation Then Rows(i).EntireRow.Delete
Next i
End Sub

Any help would be appreciated.

Hello all,

I was wondering if anyone knew of a way to do the reverse of the Delete Duplicate Function in Excel on the Data Tab.

Where the function allows you to select column headers to check for, then finds the records that are duplicates and deletes only the duplicate.

I would like a macro that does the opposite, allows me to keep all of the duplicates and delete the unique records only. Leaving me with both of the records that contained the specified column info ("=RC2&""-""&RC3&""-""&RC6").

Hi all,

This is my first post so be gentle!!

If you don't want to read the background, please feel free to skip to the 2 bullet points at the end which is what I am really after.

I am trying to set up a data analysis / reporting 'engine' @ work and I am struggling with part of it - I will try and explain as much as possible:-

We have an event log produced by some software and I want to be able to let the user delete records based on a combination of data contained in 3 cells. Here is an example of the sheet:






















The idea would be that this data feeds 3 listboxes containing a unique list of the 3 categories. In addition to displaying the name i.e. 'Event1', 'Event2' etc, can you display how many records for that entry there are - i.e. 'Event1 (2)'

The ultimate goal is for the user to be able to select an item from each list and select all the records in the sheet containing the 3 criteria, then delete those rows:

'Event2' --> 'RW' --> '6' then hit a delete button!

I am not expecting someone to do this for me, just a few pointers on how to do the main bits:-
3 listboxes, containing dynamic lists, which filter one off of the other Can you put the number of records present for that item in brackets after the description?? It's a big ask I know, but after having a look at this site it has got a lot of very tallented people, so I'm keeping my fingers crossed.

Thanks in advance,