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.

Answers


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

Need Your Help

Detecting when a handler couldn't be started when embedding Jetty

java jetty handler

I'm embedding Jetty in a similar manner as described here. When the RequestLogHandler can't open the specified logfile, it throws an exception which is unfortunately caught by org.eclipse.jetty.ser...

Why instance of class WebBaseNetscapePluginView not respond selector in safari4?

cocoa safari simbl

I am developing a plug-in with Safari. I followed the adblocker sourcecode to swizzle the method: