VBA to MATCH cells and Delete contents!

Hi All,

If it is not too much of an inconvenience to ask, would it be possible, if someone can asist me with the attached file.

Basically, the file is an extract of a large database and contains 2 sheets.

In Sheet 1, column A, there are 4 usernames: 'DAVBOR', 'KEIBRI', 'ALBCIL' and 'ANDGAL'. In sheet 1, these usernames will be unique.
In Sheet 2, column A, there will be a whole column full of these usernames. In sheet 2, there can be more than one occurence of the same username.

I would like to create a macro/VBA which would look in Sheet 2, Column A and:
- find those usernames which are present in Sheet 2 but not in Sheet 1,
- delete all those usernames- just found - from Sheet 2(i.e. delete those usernames found in Sheet 2 but not in Sheet 1)

Apprciate a lot your kind help!


Thanks a lot!

Keibri

Maybe:

Sub Keibri()
Dim i As Long, x As Long, y As Range, z
x = Sheets("Sheet2").Range("A" & Rows.Count).End(3).row
ReDim z(2 To x)
With Sheets("Sheet2")
    For i = LBound(z) To UBound(z)
        Set y = Sheets("Sheet1").Columns(1).Find(.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
            If Not y Is Nothing Then
                GoTo zz
            Else
                .Cells(i, "A").Delete xlUp
            End If
        Set y = Nothing
zz:
    Next i
End With

End Sub

Cheers guys !!! Thanks a lot for your hep. tested both solutions!

John, the only small issue I had, that when the VBA is run it will delete only the first occurrence. In the sense that if a username, which is not present in sheet 1, is repeated more than once in sheet 2, the macro will delete only the first occurrence. As for the rest, it worked great!! Thanks a lot for your help!

You're welcome. Glad to help out and thanks for the feedback. Modified.

Sub Keibri()
Dim i As Long, x As Long, y As Range, z, xx As Long, ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
x = ws2.Range("A" & Rows.Count).End(3).row
ReDim z(2 To x)
With ws2
xx = ws1.Range("A" & Rows.Count).End(3).row
    For i = LBound(z) To UBound(z)
        For Each y In ws1.Range(ws1.Cells(2, "A"), ws1.Cells(xx, "A"))
            If y.Value = .Cells(i, "A") Then
                GoTo zz
            Else
                .Cells(i, "A").Delete xlUp
            End If
        Next y
zz:
    Next i
End With
End Sub