Match and replace cell from another sheet

[sqi27]

Question: I have two sheets named Sheet1 and Sheet2. Sheets looks like this:

Sheet1
A
ab@gmail.com
je@yahoo.com
gy@hotmail.com
ob@outlook.com
vh@hotmail.com
nk@ymail.com

Sheet2
A
ob@outlook.com
je@yahoo.com
nk@ymail.com


Here is what I want: If any cell in Sheet2 Column A = Any cell in Sheet1 Column A, delete sheet1 column A cell value.


How to do it?

Thanks...

 

[cstimart]

Try this code

Sub FOO()
Dim LR As Long
Dim rng As Range

LR = Cells(Rows.Count, 1).End(xlUp).Offset(-1).Row

For Each rng In [A2].Resize(LR)
    If Not IsError(Application.Match(rng, Sheets(2).Columns(1), 0)) Then
        rng = vbNullString
    End If
Next rng

Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

 

[John Davis]

Untested. Try on a copy.

Sub sqi27()

Dim lr As Long
Dim rcell As Range

Sheets("Sheet1").Activate

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

Columns("B:B").Insert

With Range("B2:B" & lr)

    .Formula = "=VLOOKUP(A2,Sheet2!A2:A100,1,FALSE)"
    .Value = .Value
    .Replace WHAT:="#N/A", replacement:=""

End With

For Each rcell In Range("B2:B" & lr)

    If rcell.Value <> "" Then rcell.Offset(, -1).Delete shift:=xlUp
    
Next rcell

Columns("B:B").Delete shift:=xlToLeft

End Sub

 

[sqi27]

Works. Thanks.

Try this code

Sub FOO()
Dim LR As Long
Dim rng As Range

LR = Cells(Rows.Count, 1).End(xlUp).Offset(-1).Row

For Each rng In [A2].Resize(LR)
    If Not IsError(Application.Match(rng, Sheets(2).Columns(1), 0)) Then
        rng = vbNullString
    End If
Next rng

Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub