Delete entire row if value found on the other worksheet

[murrish]

Hi!
I have 2 worksheets with extensive data from patient registry. What I want to do is go through worksheet2 row by row only in a single column(A). For each cell in every row in that specific column(A) I want to find that cell in worksheet1(which is 100% there, also in first column(A)) and delete the entire row. My code seems to almost work, problem is that it is missing a few ones. Why is that? Excuse my poorly written code this is not really my field. Thx in advance!

Sub CheckA()
Dim LR As Long, i As Long, abc As Long, d As Variant
With Sheets("Sheet3")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    
    For i = LR To 1 Step -1
        
     abc = Worksheets("sheet3").Range("A" & i).Value
   
          Set d = Worksheets("sheet2").Range("A:A").Find(abc)
          If Not d Is Nothing Then
           d.EntireRow.Delete
            Else
            End If
      Next i
    
End With
End Sub

 

[Joe4]

Welcome to the Board!

Nothing obvious stands out, it looks like your code should work.

When you say "missing a few ones", what exactly do you mean?
If it deleting rows it shouldn't be, or not deleting rows that is should be?

Can you provide some simple examples of what the data in column A on each sheet looks like?

 

[murrish]

Hi and thank you! It is deleting a row correctly. When I say "missing a few" I mean that theres over 1200 rows on sheet2. All of these gets deleted correctly but a few(under 10) don't. These that are not deleted can still be found on sheet2 in column A just like those that were correctly deleted. I can obviously delete these manually but in the future there is going to be more data.
Example data from column could be any number from 500-90000.

 

[murrish]

I am unable to edit my post so sorry for double posting. When I say incorrectly deleting I mean that its not doing it at all.

 

[mumps]

Are there duplicate values in column A of Sheet2?

 

[Joe4]

Also note that the way you set up your Find, it will delete partial matches.
So, if you are looking for "32", and it finds "327", it will delete that!

If you want an exact match, use:

Set d = Worksheets("sheet2").Range("A:A").Find(abc[COLOR=#ff0000], , , xlWhole[/COLOR])

See this for all the arguments associated with FIND: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-find-method-excel

 

[murrish]

Thank you everyone!
Problem solved. I added the xwhole part and it worked. The incorrect ones were always the same and there were no doublets of them on "sheet3". 20521, 46540, 46918, 46920, 40806 were the ones if anyone is interested.