Delete rows based on match criteria in two excel files or single Excel File

[DocAElstein]

Hi

You change may work sometimes, but sometimes with different data it might not work.
In any case it is a stupid thing to do….
Let me explain…
It should be very, very simple to understand:
Consider your uploaded files ….
The range that you search in is in Ws2 (H2(SAMPLE).xlsb ) . Your data goes up to row 36
36 comes from
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row

You take each data from Ws1 (1(sample) ) and look in that range for a match. You have data in Ws1 up to 142
142 comes from
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row

That above is all correct. It is as it should be.

If you use
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
then you search in the range up to 142 in Ws2

As long as Lr1 is larger than Lr2 then it may work. But your macro may work slower, because you are searching in extra cells that are empty. You do not need to search in empty rows
If Lr1 is less than Lr2, then your macro may not work.

---

Let me try to explain again , with example of smaller data :
Lets say you have 3 lines of data ( 4 rows ) in Ws2, and 10 rows in Ws1
If you use Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row , then you will search in this range
_____ Workbook: H2(SAMPLE).xlsb ( Using Excel 2007 32 bit )

Row\Col	A
2	ADANIPOWER
3	AMARAJABAT
4	ASIANPAINT

Worksheet: Sheet2

If you use this Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row , then you will search in the range
_____ Workbook: H2(SAMPLE).xlsb ( Using Excel 2007 32 bit )

Row\Col	A
2	ADANIPOWER
3	AMARAJABAT
4	ASIANPAINT
5
6
7
8
9
10

Worksheet: Sheet2

So there will be no problems , but you are searching over a greater range than you need. So this might make your macro run slower, since you search more rows than you need to.


But now consider another example : Consider that your data in Ws2 has 7 rows, but there are only 3 rows of data in Ws1
If you use this Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row , then you will search in this range
_____ Workbook: H2(SAMPLE).xlsb ( Using Excel 2007 32 bit )

Row\Col	A
2	ADANIPOWER
3	AMARAJABAT
4	ASIANPAINT
5	AMBUJACEM
6	APOLLOHOSP
7	APOLLOPIPE

Worksheet: Sheet2

So you will search as you should in all values in Ws2

But if you use this Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row , then you will be searching over this reduced range:
_____ Workbook: H2(SAMPLE).xlsb ( Using Excel 2007 32 bit )

Row\Col	A
2	ADANIPOWER
3	AMARAJABAT

Worksheet: Sheet2

So you are not searching in all the values in Ws2, so you may get the wrong results!

---

You may choose to use Ws1 instead of Ws2. It is your choice. This will help you Fail in the future when sometimes later you will get the wrong results:
It may fail sometimes if there are more rows of data in Ws2 then in Ws1.
If it does not fail, the macro may work slower then it needs to , because it is searching in cells that it does not need to.

Alan