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

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]