Removing Duplicate Rows based on 2 columns

Hi,

I have a database with around 8 columns for people who have entered a competition with the results ordered as such:

A = Name
B = email address
C, D, E = Various other fields
F = Postcode
G = Member (always contains either "Yes" or "No")


Because the same people will enter the competition over and over my databases downloaded from the website contain duplicates. If they are logged in as members the G column will automatically asign a yes or no depending on their status.

What I want to do is remove the duplicate rows based on whether both columns A and B (name and email) both match as this is how I am identifying unique people, NOT if the whole row matches (to avoid excluding people based on typos in addresses etc - name and email is accurate enough for what I need). I want the duplicate rows to be deleted in their entirety, preserving the A-G column structure for the uniques that remain.

This is the code I am using as found on an Excel resource website...

Sub RemoveDuplicateRecords()
    ' Local variables.
    Dim rngData         As Range, cell  As Range
    ' Set the data range based on email column.
    Set rngData = ActiveSheet.Range("B1", ActiveSheet.Range("B1").End(xlDown)).Offset(0, -1)
    ' Sort the table by name + email address.
    rngData.EntireRow.Sort Key1:=rngData.Range("A1").Offset(0, 1), Order1:=xlAscending, Key2:=rngData.Range("A1").Offset(0, 2), Order2:=xlAscending, Key3:=rngData.Range("A1").Offset(0, 0), Order3:=xlAscending
    ' Remove duplicate entries based on name + email as a duplicate.
    ' For speed purposes use clearcontents and then resort list
    For Each cell In rngData
    If cell.Offset(0, 1) = cell.Offset(1, 1) And cell.Offset(0, 2) = cell.Offset(1, 2) Then
    If cell.Offset(1, 0) = "" And cell.Offset(0, 0) <> "" Then cell.Offset(1, 0) = cell.Offset(0, 0)
    cell.EntireRow.ClearContents
    End If
    Next cell
    ' Sort the table alphabetically by name.
    rngData.EntireRow.Sort Key1:=rngData.Range("A1").Offset(0, 0), Order1:=xlAscending, Key2:=rngData.Range("A1").Offset(0, 1), Order2:=xlAscending
End Sub

--

The code above works but my problem is that sometimes people will enter a competition when they are logged in as a member, and then again when they are not logged in, and thus I get duplicates which are idential across name and email but has different Yes or No values in column G.

I thought the macro preserved the last entry in the list (deleting all duplicated before it) but I have noticed that one time it did not (which is how I discovered the Yes and No duplicate entries for someone).

Can someone please explain how to modify the code to make sure its as solid and reliable as possible, and can we add in something to say "if there are Yes and No values in column G for duplicated rows based on name and email, I want to keep the YES value in my summarised list once the macro has finished".

I hope that makes sense and that I have included everything needed for someone with more VBA experience than me to assist with!!


Susie x