Excel VBA: code only delete about half of the rows [closed]

I have the following code that changes the font color of cells to red if the cell value is found in both Sheets("950") and Sheets("65001"). When I changed the code from changing color to deleting entire row, only about half of the rows were deleted. The number of rows started from 75835. After the first iteration, 38095 rows were left; after the second iteration, 19133 rows were left, then 9610, 4828, 2425, 1219. Rows that should not be deleted (not found in both sheets) were also deleted. I had tried reversing the loop (changing i = 1 To UBound(arr) to i = UBound(arr) to 1 step -1 before deleting) to no avail.

Two questions:

How to delete undesired rows in one iteration?

    Set rng1 = ThisWorkbook.Sheets("65001").Range("D1:D75994")        Dim rng2 As Range   'small array      Set rng2 = ThisWorkbook.Sheets("950").Range("D1:D75835")    '38095, 19133, 9610, 4828, 2425, 1219        Dim i As Long, j As Long, k As Long      Dim arr As Variant      Dim Matches As Object: Set Matches = CreateObject("Scripting.Dictionary")            Application.ScreenUpdating = False            arr = rng1.Value            For i = 1 To UBound(arr)          For j = 1 To UBound(arr, 2)              If Not Matches.Exists(arr(i, j)) Then Matches.Add arr(i, j), 1          Next j      Next i        k = 0            'input the shorter data inside an array      arr = rng2.Value      For i = 1 To UBound(arr)          For j = 1 To UBound(arr, 2)              If Matches.Exists(arr(i, j)) Then                  'your code if the value is found                  Sheets("950").Cells(i, 4).Font.Color = RGB(255, 0, 0)  '                Sheets("950").Cells(i, 4).EntireRow.Delete                  k = k + 1              End If          Next j      Next i  

https://stackoverflow.com/questions/65596297/excel-vba-code-only-delete-about-half-of-the-rows January 06, 2021 at 09:04PM