How to compare arrays and delete entire row when condition is met

See if this is how you wanted.

Sub test()
    Dim s1 As Range, s2 As Range, i As Long, ii As Long
    Dim iii As Long, iv As Long, v As Long, n As Long, x
    Application.ScreenUpdating = False
    Set s1 = Sheets("s1").Cells(1).CurrentRegion
    Set s2 = Sheets("s2").Cells(1).CurrentRegion
    For i = s2.Rows.Count To 1 Step -1
        For ii = 2 To s2.Columns.Count Step 10
            For iii = 1 To s1.Rows.Count
                x = WorksheetFunction.IfError(Application.Match( _
                    s2.Cells(i, ii).Resize(, 10), s1.Rows(iii), 0), 0)
                For iv = 2 To s2.Columns.Count Step 10
                    n = 0
                    For v = 1 To 10
                        If (x(v) >= iv) * (x(v) <= iv + 9) Then n = n + 1
                        If n > 1 Then
                            s2.Rows(i).EntireRow.Delete
                            Exit For
                        End If
                    Next
                    If n > 1 Then Exit For
                Next
                If n > 1 Then Exit For
            Next
            If n > 1 Then Exit For
        Next
    Next
    Application.ScreenUpdating = True
End Sub

Hi Jindon,

That's it! Thank you very much.

I would like to ask you one more favor. The previous sample file had worksheets "S1" and "S2" with only one group of 500 cells. Now, worksheets "S1" and "S2" have 4 groups of 500 cells. Could you please adapt your macro?

The sample file attached has 2 worksheets. In the worksheet "S2", the row with the number "102" in column "A" should be deleted after macro execution (please update "n>1" to "n>4", as this time I would like to delete the rows that have a group of 10 cells with more than 4 elements in common).

Thanks.

I don't understand how you want.

The first one is comparing every 10 columns of data in a row of S2 with every 10 columns of data in all rows in S1 and delete when it finds more than one match.

How do you want to compare now?

Hi Jindon,

I still need to compare every 10 columns of data in a row.
Your code compares each one of the 50 groups of 10 cells in columns "B" to "SG" from S2 to:
- Each one of the 50 groups of 10 cells in columns "B" to "SG" from S1.

Now, "S1" and "S2" have 4 groups of 500 cells:
1- Columns "B" to "SG", with 50 groups of 10 cells ("B" to "K", "L" to "U", and so forth);
2- Columns "SI" to "ALN", with 50 groups of 10 cells ("SI" to "SR", "SS" to "TB", and so forth);
3- Columns "ALP" to "BEU", with 50 groups of 10 cells ("ALP" to "ALY", "ALZ" to "AMI", and so forth);
4- Columns "BEW" to "BYB", with 50 groups of 10 cells ("BEW" to "BFF", "BFG" to "BFP", and so forth);

I need to compare each one of the 50 groups of 10 cells in columns "B" to "SG" from S2 to:
- Each one of the 50 groups of 10 cells in columns "B" to "SG" from S1;
- Each one of the 50 groups of 10 cells in columns "SI" to "ALN" from S1;
- Each one of the 50 groups of 10 cells in columns "ALP" to "BEU" from S1;
- Each one of the 50 groups of 10 cells in columns "BEW" to "BYB" from S1.

Then, I need to compare each one of the 50 groups of 10 cells in columns "SI" to "ALN" from S2 to:
- Each one of the 50 groups of 10 cells in columns "B" to "SG" from S1;
- Each one of the 50 groups of 10 cells in columns "SI" to "ALN" from S1;
- Each one of the 50 groups of 10 cells in columns "ALP" to "BEU" from S1;
- Each one of the 50 groups of 10 cells in columns "BEW" to "BYB" from S1.

Then, I need to compare each one of the 50 groups of 10 cells in columns "ALP" to "BEU" from S2 to:
- Each one of the 50 groups of 10 cells in columns "B" to "SG" from S1;
- Each one of the 50 groups of 10 cells in columns "SI" to "ALN" from S1;
- Each one of the 50 groups of 10 cells in columns "ALP" to "BEU" from S1;
- Each one of the 50 groups of 10 cells in columns "BEW" to "BYB" from S1.

Then, I need to compare each one of the 50 groups of 10 cells in columns "BEW" to "BYB" from S2 to:
- Each one of the 50 groups of 10 cells in columns "B" to "SG" from S1;
- Each one of the 50 groups of 10 cells in columns "SI" to "ALN" from S1;
- Each one of the 50 groups of 10 cells in columns "ALP" to "BEU" from S1;
- Each one of the 50 groups of 10 cells in columns "BEW" to "BYB" from S1.

In this new macro, please update "n>1" to "n>4". After running this new macro on "Sample File - 2.xlsx", in the worksheet "S2", the row with the number "102" in column "A" should be deleted.

Thanks.

OOps, so sorry, I didn't realize s1 has more columns up to col.BYB...

Hope this works as you wanted.

Sub test()
    Dim s1 As Range, s2 As Range, myAreas As Areas, myArea As Range, r As Range
    Dim i As Long, ii As Long, iii As Long, iv As Long, v As Long, x, n As Long
    Const myStep = 10, myNum = 4
    Application.ScreenUpdating = False
    Set s1 = Sheets("s1").Cells(1).CurrentRegion
    Set s2 = Sheets("s2").Cells(1).CurrentRegion
    For i = 1 To s2.Rows.Count
        For ii = 2 To s2.Columns.Count
            For iii = 1 To s1.Rows.Count
                Set myAreas = s1.Rows(iii).EntireRow.Resize(, Columns.Count - 1).Offset(, 1).SpecialCells(2, 1).Areas
                For Each myArea In myAreas
                    x = WorksheetFunction.IfError(Application.Match(s2.Cells(i, ii).Resize(, myStep), myArea, 0), 0)
                    For iv = 1 To myArea.Count Step myStep
                        n = 0
                        For v = 1 To myStep
                            If (x(v) >= iv) * (x(v) <= iv + myStep) Then n = n + 1
                            If n > myNum Then
                                If r Is Nothing Then
                                    Set r = s2.Rows(i)
                                Else
                                    Set r = Union(r, s2.Rows(i))
                                End If
                                Exit For
                            End If
                        Next
                        If n > myNum Then Exit For
                    Next
                    If n > myNum Then Exit For
                Next
                If n > myNum Then Exit For
            Next
            If n > myNum Then Exit For
        Next
    Next
    If Not r Is Nothing Then
        r.EntireRow.Delete
    Else
        MsgBox "No match"
    End If
    Application.ScreenUpdating = True
End Sub

Thank you Jindon. You did a great job!