excel: check for duplicate rows based on 3 columns and keep one row
This might be a bit much to ask but I'm wondering if the following is possible. this is all based on my initial question on Copy Range and Paste Values in another Sheet's specific Range
I have a sheet that contains approximately 12 columns where the same type of data is copied to which means that two or more rows could contains the exact same data, however if I could check for duplicates it would be based on 3 specific columns.
I don't want to use AutoFilter or Conditional Formatting for this task because the sheet can contain hundreds to thousands of rows and I want to be able to locate duplicates, delete them and keep just one original. I have also read other posts on here about Removing Duplicates based on column conditions but so far none has worked for me.
The Range of data is from A thru P and down and the 3 columns that would identify a duplicate are A (which is numeric, an ID), B (which is a date), and P (which is a Text string). Where this data comes from, it will always have the same format and will always be placed in the same columns which is why if A,B,P are the same, then it is considered a duplicate.
Thanks for the help in advance.
This code works for me (lastrow based on this asnwer by @SiddharthRout):
Sub test() Dim lastrow As Long With ThisWorkbook.Worksheets("Sheet1") If Application.WorksheetFunction.CountA(.Cells) <> 0 Then lastrow = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Else lastrow = 1 End If 'Array(1, 2, 16) means 1 - for A, 2 for B and 16 for P columns .Range("A1:P" & lastrow).RemoveDuplicates Columns:=Array(1, 2, 16), _ Header:=xlYes End With End Sub