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

See if this is how you wanted.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Thank you Jindon. You did a great job!