Comparing two columns on different sheets and deleting the entire row if any cell is different

[Moonbeam111]

I have a workbook with two sheets open. There is a column A in sheet 1 with data from rows A1:A427 and column A in sheet 2 with data from A1:A355. I would like for every cell in column A sheet 2 from range A1:A355 be checked individually against every cell in sheet 1 column A1:A427. (They are alphabetized but I have extra data in sheet 1's columns I don't need). If the code finds a cell in sheet 1 that isn't in sheet 2, I would like for that entire row to be deleted from it in sheet 1. Is something like that possible to do with a macro?

Sub Sheet1_Button1_Click()
  Dim cell As Range, f As Range
 
  For Each cell In Worksheets("Sheet1").Range("A2")
  If cell <> Worksheets("Sheet2").Range("A2:A355") Then
  cell.EntireRow.Delete
  End If
  Next cell
End Sub

[CODE=vba]

That didn't work.

 

[alansidman]

Since you have O365, why not keep it simple and use an inner join with Power Query. No coding required.

 

[Moonbeam111]

Office 365 runs slowly on this particular workbook. I made it in Office 2010 and it seems to work best using that version for some reason. (Trust me I've tried)

 

[Logit]

Option Explicit

Sub DeleteNotMatch()
Const sh1Col As String = "A" ' << sheet1 data in col A, change
Const sh2Col As String = "A" ' << sheet2 data in col A, change
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r1 As Long, r2 As Long, i As Long
Dim r As Range

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

r1 = ws1.Cells(Rows.Count, sh1Col).End(xlUp).Row
r2 = ws2.Cells(Rows.Count, sh2Col).End(xlUp).Row

For i = r1 To 2 Step -1
    For Each r In ws2.Range(sh2Col & "2:" & sh2Col & r2)
        If ws1.Cells(i, sh1Col).Value = r.Value Then GoTo myNext
    Next r
    ws1.Cells(i, sh1Col).EntireRow.Delete
myNext:
Next i
End Sub

 

[Moonbeam111]

Logit: That code is nice but it seems to not work correctly. I had 427 cells of data in sheet1 and the macro reduced it to 255. Sheet2 has 355 cells of data. The two sheets should have the same amount when it is finished because all data in sheet1 is in sheet2 but sheet1 has extra cells that don't match that i don't want..

 

[Logit]

The macro compares values in Col A of both sheets.
Did I misread your original posting ?

 

[Moonbeam111]

I am not sure, you may want to try it yourself. I can partially understand this macro but it just seems to deleting more rows than it should in Col A of Sheet1.

For example pretend the first column here is sheet 1 column A. And the second column is sheet 1 Col A. I would like for the macro to delete "P", "O", "M" rows from column A sheet 1because they are not in column A sheet 2.

A	A
B	B
P	C
C	D
O	E
D	F
M	G
E
F
G

But your macro is deleting more than that. For example, its deleting B and C. Which are already in the second sheet col A and dont need to be deleted.

A correct final result would look like this, in this example:

A	A
B	B
C	C
D	D
E	E
F	F
G	G

 

[Logit]

I just tried the macro here, using the letters you provided.

This list:

A
B
P
C
O
D
M
E
F
G

I put in Sheet 1, Col A.

This list :

A
B
C
D
E
F
G

I put in Sheet 2, Col A.

This was the result in Sheet 1 :

A
B
C
D
E
F
G

 

[Moonbeam111]

You are correct. Then my values must not match 100% across both columns. I'll have to look into that. But your macro works as I originally intended so I marked it as the solution.

 

[Logit]

Glad you have an answer. Cheers !