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

**DocAElstein** · 07-17-2020, 02:04 PM

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** · 07-17-2020, 02:46 PM

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)

Code:

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** · 07-17-2020, 05:00 PM

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** · 07-17-2020, 05:18 PM

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

**fixer** · 07-17-2020, 05:18 PM

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

**DocAElstein** · 07-18-2020, 01:28 AM

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

Code:

        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

Code:

        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

Code:

        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** · 07-18-2020, 01:55 AM

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

**fixer** · 07-21-2020, 12:35 PM

Hi Experts,

Code:

Sub STEP6()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 5000: Lr2 = 5000
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Ws1 = Wb1.Worksheets(1)
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\Error.xlsx")
Set Ws2 = Wb2.Worksheets(1)

Dim rngSrch As Range: Set rngSrch = Ws2.Range("C1:C" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
    For Cnt = Lr2 To 1 Step -1
    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
         rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        Else
        End If
        
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub

This code has limitation of LR1=5000 & LR2= 5000(plz remove the limitations of this macro)
& one more issue is there with this macro
I am sending the sample file plz run the macro & see the output
If error.xlsx is blank sheet then it is giving something different output plz see

**mail** · 07-23-2020, 10:53 AM

https://eileenslounge.com/viewtopic....271877#p271877
https://eileenslounge.com/viewtopic.php?f=30&t=35042

**DocAElstein** · 07-26-2020, 02:51 PM

Originally Posted by fixer

This code has limitation of LR1=5000 & LR2= 5000(plz remove the limitations of this macro)

Avinash,
Your apparent ability or unwillingness to understand or remember anything is now at such a high level that
Either
_1) You are trying deliberately as hard as possible to annoy us
or
_2) If _1) is not correct, then you really should now see a Doctor or Psychologist, since you are very likely seriously mentally Ill

We have had the making last row dynamic issue over and over again. Even just recently almost an identical question from you was answered, and the macro was also almost identical!!!:
https://eileenslounge.com/viewtopic.php?f=30&t=34937
https://eileenslounge.com/viewtopic....271316#p271316
https://excelfox.com/forum/showthrea...ll=1#post14565

Edit: And you posted this same question a few days before.. Bro you are in a total mixed up mess!!!
(https://excelfox.com/forum/showthrea...ll=1#post14599 )

Originally Posted by fixer

one more issue is there with this macro
I am sending the sample file plz run the macro & see the output
If error.xlsx is blank sheet then it is giving something different output plz see

I have no idea what you are asking. I have no idea what it is that you are trying to say .
Possibly it is something to do with problem 2 here:
https://excelfox.com/forum/showthrea...ll=1#post14565
The problem is your usual incompetence. You mix everything up, and half the time either don’t know what you want or cannot explain what you want. This is not a VBA issue. This is a problem with your brain that needs medical help.

Alan

Edit.. and here we go yet once again .... same question
https://eileenslounge.com/viewtopic....272085#p272085
_.. same wrong answer...
https://eileenslounge.com/viewtopic....272094#p272094
( wrong caslculation of Lr2 )
_... and yet agin you think its right ... and another set of duplicated cross postings will begin when you realise yet agin that it is wrong... Bro - you need medical help, fast! NO JOKE!