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

[fixer]

This was the Macro

Sub STEP3()
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)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long: 
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & 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
         
        Else
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        End If
        
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub

I changed this Macro As per my needs but getting error
So Plz have a look Sir

Sub STEP6()
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)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & 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

Plz see the else statement in both the macros

Error which i got, I uploaded the pic of the same




https://eileenslounge.com/viewtopic....271392#p271392



Plz run the macro the file which i have uploaded

[DocAElstein]

We are back to about a year ago, around here
https://excelfox.com/forum/showthrea...ll=1#post11467
https://excelfox.com/forum/showthrea...ll=1#post12896
https://excelfox.com/forum/showthrea...ll=1#post12897

---

problem solved .. please delete Thread

No. Problem is not solved
I still see at least two issues

Problem 1
You still do not understand Lr issue. You still have it wrong….
Lr variable, we have typically used in many of your macros , ( in the meantime well over a hundred similar macros ) , to determine dynamically the last used cell in a column.

For example, in your macro, for the first column in the two worksheets :-
Lr1 is for last row dynamically found for worksheet, Ws1
Lr2 is for last row dynamically found for worksheet, Ws2

So it should be

Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row

Problem 2

        If Not MtchedCel Is Nothing Then
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        Else
        
        End If

Or

        If Not MtchedCel Is Nothing Then
        
        Else
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        End If

You always get that mixed up. Problem is English language and general idiocy. You may never know what you want. Half the time you will get it correct. The other half of the time you will get it wrong.

---

Most of the time it continues to appear as if you either do not read or understand anything that anyone anywhere ever writes for you.
You continue to go around in chaotic circles of multiple duplicate cross posting of the same or very similar issues, and seem to be starting again recently, with even more cross posting of very similar questions.
Because of this you are now experiencing even more problems: Everyone has a different posting style, and there are usually many ways to solve a problem in VBA. So you are producing a chaotic collection of coding written by many different people, in many different ways. This collection of coding, some working perfectly, some not erroring, but not working properly, you will never, ever, be able to combine to achieve anything over than a total chaotic mess….