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

[fixer]

I would like to notify something about this macro and about this post
in this macro

Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row   ' it will be ws1, not ws2 bcoz by using ws1 i am getting correct output

plz have a look to the sample file & macro too...
Problem is Solved according to me But any more suugesstion is welcome

[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

[fixer]

Plz run this macro Doc sir & Plz see the output
This is the updated macro which u have provided Doc Sir & i sent u the sample file today plz run & see the output u will understand (This macro is not giving me the desired output)

Sub STEP3() '  https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14587&viewfull=1#post14587    https://eileenslounge.com/viewtopic.php?f=30&t=34937
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)                                   ' First worksheet tab counting from the left
Set Ws2 = Wb2.Worksheets.Item(2)                                   ' Second worksheet tab cunting from the left
Dim Lr1 As Long, Lr2 As Long
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row       ' Dynamically getting the last row in worksheet referenced by  Ws1
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row       ' Dynamically getting the last row in worksheet referenced by  Ws2
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")   ' The range that will be searched in
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")     ' The range from which data will be looked for in rngSrch

Dim Cnt As Long  '    For  each  rngDta.Item(Cnt)
    For Cnt = Lr2 To 1 Step -1                                     ' We take  -ve  steps = we go backwards. This is important when deleteing things.  See: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12902&viewfull=1#post12902
    Dim MtchedCel As Variant
     Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then ' Range.Find  would return nothing if it did not find a match
              ' If it was  Not  Nothing  then there was a match. So we do nothing
        Else  ' The attempt at a match failed, we got  Nothing  this is the condition to delete
         rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp             ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet  Up
        End If
        
    Next Cnt             ' Next  rngDta.Item(Cnt)
 Wb1.Close SaveChanges:=True                                       ' Save the file and close it
 Wb2.Close SaveChanges:=True                                       ' Save the file and close it
End Sub

[DocAElstein]

When I run the macro it does the following:

It considers values in Ws1 column B , ( that is the data range, rngData )
If it finds that value in Ws2 column A , then it does nothing
If it does not find that value, then it deletes that row in Ws1

With your data, it does not find ADANIPORTS or ADANIENT or ACC , so those data rows in Ws1 are deleted.

Looking at your Output worksheet, it would appear that you should be deleting many more rows in Ws1

I have tried to look back through the chaos of duplicated cross postings that you have done to try and geuss what you want….

The last macro I gave you is looping through the data in Ws1 using this code line
For Cnt = Lr2 To 1 Step -1
Most of the last macros you posted here and elsewhere were doing that……….
That is going to only loop through the data in Ws1 up to 36 ( because Lr2 is 36 )
I will take a guess that you want to loop through all the data in Ws1 , because I can see no reason why you should loop through just some of your data.

My guess is that you should be doing this
For Cnt = Lr1 To 2 Step -1

So

Try this next macro version:……
https://excelfox.com/forum/showthrea...ll=1#post14636

---

So finally what this macro is doing. In English:
Data values in Ws1 , (first worksheet in "1(sample).xls") column B , are looked for ( attempted to be matched ) to the column A range in Ws2 ( second worksheet in "H2(SAMPLE).xlsx")
If a match is found, then nothing is done. If a match was not found, then the entire row containing the data value in Ws1 is deleted

[fixer]

Problem Solved
Awesome Doc Sir
Thnx Alot for helping me in solving this Problem Sir
Have a Awesome Day

[fixer]

Hi Experts,
If column A of H2.xlsx matches with column B of 1.xls then delete that entire row by vba
macro will be placed in macro.xlsm and files can be located anywhere in the pc so hardcoded the path so that i can change it as per my needs

Thnx for the Help


sample file attached below

[DocAElstein]

Your description is bad.
You have not said what worksheets are to be considered. It appears to be the second worksheet in "H2(SAMPLE).xlsx" and the first worksheet in "1(sample).xls"

---

So your last question I answered for you this morning was… , in English :
_ Sub Step3b() https://excelfox.com/forum/showthrea...ll=1#post14636
Data values in Ws1 , (first worksheet in "1(sample).xls") column B , are looked for ( attempted to be matched ) to the column A range in Ws2 ( second worksheet in "H2(SAMPLE).xlsx")
If a match is found, then nothing is done. If a match was not found, then the entire row containing the data value in Ws1 is deleted

Now, the question here is….
Data values in Ws1 , (first worksheet in "1(sample).xls") column B , are looked for ( attempted to be matched ) to the column A range in Ws2 ( second worksheet in "H2(SAMPLE).xlsx")
If a match is not found, then nothing is done. If a match is found, then the entire row containing the data value in Ws1 is deleted


It is sometime very hard to believe that you are incapable of figuring out the changes for yourself!
If you had left the ' comments on my macro then the changes would have been obvious

Previous macro,
Sub STEP3b() If a match is found, then nothing is done. If a match was not found, then the entire row containing the data value in Ws1 is deleted

        If Not MtchedCel Is Nothing Then ' Range.Find  would return nothing if it did not find a match
              ' If it was  Not  Nothing  then there was a match. So we do nothing
        Else  ' The attempt at a match failed, we got  Nothing  this is the condition to delete
         rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp             ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet  Up
        End If
        

New Macro for If a match is not found, then nothing is done. If a match is found, then the entire row containing the data value in Ws1 is deleted

        If Not MtchedCel Is Nothing Then ' Range.Find  would return nothing if it did not find a match.  Not  Nothing is the condituion of a match, the condition to delete the row
         rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp             ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet  Up
        Else  ' The attempt at a match failed, we got  Nothing  this is the condition to do nothing
        ' If it was   Nothing  then there was not a match. So we do nothing
        End If
    

Or

        If MtchedCel Is Nothing Then  ' Range.Find  would return nothing if it did not find a match.    Nothing is the condituion of no match, the condition to do nothing
         ' If a match is not found, then nothing is done
        Else  ' The attempt at a match was succesful, we got  a match, the condition to delete the row
         rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp             ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet  Up
        End If
    

Full macros here:
https://excelfox.com/forum/showthrea...ll=1#post14641




Alan

[fixer]

Thnx Alot Doc Sir for helping me in solving this problem
Problem Solved
Have a Awesome Day

From next Time, i will try to provide as much info as much i can provide

[DocAElstein]

Problem 1 is very simple. You must have done this already hundreds of time! There is only one very important thing to remember: When looping to delete things, you must always loop backwards : https://excelfox.com/forum/showthrea...ll=1#post12902

Before:
_____ Workbook: ABC-PROBLEM1.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1265	1282.7	1246.5	1275.3	1247	22	BUY	14104.65
3	NSE	ADANIENT	EQ	151.85	165.45	151.4	151.85	152.35	25	BUY	3235.03
4	NSE	ADANIPORTS	EQ	348	348	348	346.55	338.85	15083	BUY	3539.747

Worksheet: 1-Sheet1

Run macro:

Sub DeleteRowifHighequalsLow_2() ' Problem 1   https://www.excelforum.com/excel-programming-vba-macros/1321862-delete-entire-row-by-vba.html   https://excelfox.com/forum/showthread.php/2582-delete-entire-row-by-vbA
Rem 1 Worksheet info
Dim WbABC As Workbook, WsABC As Worksheet
 Set WbABC = Workbooks.Open(ThisWorkbook.path & "\ABC-PROBLEM1.xls")
 Set WsABC = WbABC.Worksheets.Item(1)
Dim LrABC As Long: Let LrABC = WsABC.Range("E" & WsABC.Rows.Count & "").End(xlUp).Row  '   Make Lr dynamic    https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466
Dim arrABC() As Variant
 Let arrABC() = WsABC.Range("A1:K" & LrABC & "").Value
Dim Cnt As Long
    For Cnt = LrABC To 2 Step -1   '      When looping to delete things, you must always loop backwards :   https://excelfox.com/forum/showthrea...ll=1#post12902  
        If arrABC(Cnt, 5) = arrABC(Cnt, 6) Then
         WsABC.Rows(Cnt).EntireRow.Delete Shift:=xlUp             ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet  Up
        Else
                
        End If
    Next Cnt
End Sub

After:
_____ Workbook: ABC-PROBLEM1.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1265	1282.7	1246.5	1275.3	1247	22	BUY	14104.65
3	NSE	ADANIENT	EQ	151.85	165.45	151.4	151.85	152.35	25	BUY	3235.03
4

Worksheet: 1-Sheet1

---

Problem 2
See next post

[DocAElstein]

From last post
Problem 2
You have not given me an After, and once again the explanation is bad. - you did not yet answer maniacb at excelforum.... https://www.excelforum.com/excel-pro...ml#post5365612

I will have to assume that this:
If column B of DEF.xlsx matches with Column I of ABC.xls then delete that entire row
Is supposed to mean this
If column B of DEF.xlsx matches with Column I of ABC.xls then delete that entire row of ABC.xls
In other words, something we have done many times already….
We consider the data in column I of ABC.xls from row 2. We try to find ( match ) that data to any row of data in column B of DEF.xlsx
If we do have a match then we delete the entire row containing the considered data from Column I of ABC.xls

I will do the first solution, Problem2a) slightly differently ,because I am getting bored answering almost the same question over and over again…
I am using vba arrays because for your values work that is the best approach in Excel ( https://excelfox.com/forum/showthrea...ll=1#post14628 )
I do not delete rows, so I do no backward looping
Instead I collect indices of the rows I want to have = rows which are not deleted. For you test data, the rows I want are 1 4 5 6 7 8 9 ( rows 2 and 3 ) are not wanted – you want to delete them )
Finally the macro replaces the original range with a new one containing just the rows you want

I also did a more conventional solution Problem2b like the ones you have seen a lot of in the last few days. It deletes the rows ( rows 2 and 3 )


Before
_____ Workbook: DEF PROBLEM 2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	NSE	22	6	>	12755	A					GTT
2	NSE	25	6	<	13448	A					GTT

Worksheet: DEF

_____ Workbook: ABC.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1265	1282.7	1246.5	1275.3	1247	22	BUY	14104.65
3	NSE	ADANIENT	EQ	151.85	165.45	151.4	151.85	152.35	25	BUY	3235.03
4	NSE	ADANIPORTS	EQ	348	348	348	346.55	338.85	15083	BUY	3539.747
5	NSE	APOLLOHOSP	EQ	1359	1391	1339	1355.65	1346.8	157	BUY	2994.65
6	NSE	APOLLOPIPE	EQ	351.95	351.95	340	350.8	347.2	14361	BUY	4339.566
7	NSE	ASHOKLEY	EQ	52.1	52.35	51	52.7	51	212	BUY	7051.82
8	NSE	AUROPHARMA	EQ	789	805.45	775.35	796.95	782.4	275	BUY	3608.73
9	NSE	AXISBANK	EQ	426.5	435.55	408.7	427.45	413.5	5900	BUY	255575.45

Worksheet: 1-Sheet1 Problem 2

After
_____ Workbook: ABC.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ADANIPORTS	EQ	348	348	348	346.55	338.85	15083	BUY	3539.747
3	NSE	APOLLOHOSP	EQ	1359	1391	1339	1355.65	1346.8	157	BUY	2994.65
4	NSE	APOLLOPIPE	EQ	351.95	351.95	340	350.8	347.2	14361	BUY	4339.566
5	NSE	ASHOKLEY	EQ	52.1	52.35	51	52.7	51	212	BUY	7051.82
6	NSE	AUROPHARMA	EQ	789	805.45	775.35	796.95	782.4	275	BUY	3608.73
7	NSE	AXISBANK	EQ	426.5	435.55	408.7	427.45	413.5	5900	BUY	255575.45
8
9

Worksheet: 1-Sheet1 Problem 2

Macros
Here 2a
https://excelfox.com/forum/showthrea...ll=1#post14645
Or
Here 2b
https://excelfox.com/forum/showthrea...ll=1#post14646