Forum Discussion

cmccully's avatar
cmccully
Copper Contributor
Mar 13, 2020

Remove rows based on duplicate values in one column and values in another column

I have a spreadsheet with many groups of duplicate values in one column. I need to remove effected rows based on dates in another column. Below is a screen shot of the data. As you can see, column A has many unique values but also values that are duplicated on multiple rows. I need to remove the rows that have a duplicate value in column A but have a date <> 12/31/9999. Since many other valid rows have other dates I am not sure how to remove the duplicate rows based on this date while leaving the rows with a unique column A intact. Just to be clear, I want to remove rows 254-256 and leave row 257 intact. The spreadsheet has over 6,000 rows so doing this manually is out of the question. Any help would be much appreciated.

 

excel

1 Reply

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Mar 13, 2020

    cmccully 

    You may try the below macro to delete the rows where column A has duplicate values but the date in column I is not 12/31/9999.

     

    Sub DeleteRows()
Dim lr  As Long
Dim i   As Long

Application.ScreenUpdating = False

lr = Cells(Rows.Count, "A").End(xlUp).Row

For i = lr To 2 Step -1
    Cells(i, 1).Select
    If Application.CountIf(Columns(1), Cells(i, 1)) > 1 Then
        If Cells(i, "I") <> #12/31/9999# Then
            Rows(i).Delete
        End If
    End If
Next i
Application.ScreenUpdating = True
End Sub

Resources