[Solved] Excel keep 1st three rows and delete rest of duplicates [closed]

by


So, going the VBA route, you’ll need to loop through each row in your tab and test the value to see if it’s a duplicate, if it’s a duplicate then you’ll increase a counter variable and once that counter variable hits 3 you start deleting rows.

This is a little complicated if you haven’t worked with VBA. Please take some time to play with the code and understand it. I’ve written comments in it to help out.

Sub keepFirstThreeDuplicates()
    Dim workingRow As Integer
    Dim currentDup As String
    Dim dupCounter As Integer
    Dim wsheet As Worksheet

    'change this to your tab name
    Set wsheet = ThisWorkbook.Sheets("Sheet1")

    'loop through every row just guessing that your data starts at row 1 (A1) and goes to 50000 (A50000)
    For workingRow = 1 To 50000

        If workingRow = 1 Then 'we are at the first row, so grab the value and set dupCounter to 1
            currentDup = wsheet.Cells(workingRow, 1).Value 'Assuming column 1, so this is Cell A1
            dupCounter = 1
        ElseIf currentDup = wsheet.Cells(workingRow, 1).Value Then 'we have another duplicate
            If dupCounter = 3 Then 'We already have three duplicates, so delete the row, and set the row back one (because we deleted the row)
                wsheet.Rows(workingRow).Delete
                workingRow = workingRow - 1
            Else
                dupCounter = dupCounter + 1
            End If
        Else 'We are at a new value, so grab the value and set dupCounter to 1
            currentDup = wsheet.Cells(workingRow, 1).Value
            dupCounter = 1
        End If

        'exit the for loop if we hit a blank
        If currentDup = "" Then Exit For
    Next workingRow

End Sub

If you are super new to VBA, to use this code:

  1. While in your workbook, hit Ctrl+F11 to get to the Visual Basic
    Editor (VBE)

  2. Your workbook will be called a “VBAProject” in the VBAProject panel.
    Right click on it and select Insert>>Module

  3. Double click your new module “Module1” to open it.

  4. Paste in this code.

  5. To run it, click somewhere in the code and hit the play button up
    top (or F5 on your keyboard). Make sure you edit the code to suit
    the needs of your workbook like changing the tab name to your tab.
    Also make sure to back up your workbook before running it as this
    will delete rows and you won’t be able to undo.

Lastly, this is just one way to do it. It’s not meant to 100% address your needs since I’m guessing at number of rows, which column your data is in, that the data is already sorted by your duplicate column, and other whatnot. This is just to point you in a solid direction in VBA. It worked on my test workbook for a fake list I created in Column A of a fresh workbook.

solved Excel keep 1st three rows and delete rest of duplicates [closed]