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

Problem Solved
Thnx Alot Doc Sir for helping me in solving this problem Sir

This was the Macro

Code:

---

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

Code:

---

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

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

---

Quote:

---

Originally Posted by fixer

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

Code:

---

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

---

Or

Code:

---

        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….

Problem 1)

Code:

---

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

---

R u talking about this Doc Sir
I have used this macro
Do u mean to say that
I have to use this in this macro

Code:

---

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

---

Problem 2

https://eileenslounge.com/viewtopic.php?f=30&t=34937

U r talking about this
so may i know what changes is required in the second problem so that i can put the same & be safe




3)Thnx Bro for all of ur Great Efforts, this is Best thing about u
Thnx for knowing me about the problems
& Yes this question i asked u in last year , & it is working fine
I just want to make the system Awesome Like u Bro
U r the one of the best person i ever met
Thnx for keeping a eye into all of my problems that i may have infuture

Problem 1)

Quote:

---

Originally Posted by fixer

Problem 1)
Do u mean to say that
I have to use this in this macro

Code:

---

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

---

..

---

Yes

---

Problem 2)
This is not a VBA problem.
You cant speak English or understand any simple thing in English.
I am not sure what the solution to such problems are.
Possibly try to leave in some 'green comments so then later you will remember what code line is doing. Then later it is easier to remember and later it is easier to see where is problem
'green comments do not cause any problems
'green comments do not make any coding slower
There will never be issues caused by 'green comments
'green comments can stay for ever. They do no harm
VBA does not see 'green comments. So VBA is not effected by 'green comments For VBA , 'green comments are invisible. So 'green comments cannot have any effect on any coding
There will never be any problems caused by 'green comments
There can never be any problems caused by 'green comments
A code line can be very long. So you can have a very long ling full of important information so that later you can see what all code line are doing.
So if you have lots of 'green comments you will not need to remember, because all information is there for you to read later if you need to know again what is the code doing.

But everyone has a different choice. You can choose

Some people use no 'green comments
Some people use some 'green comments
Some people use lots of 'green comments
I use very many 'green comments because I am not smart. I am stupid. I forget everything. I am not a computer programmer professional. I am a stupid Builder. I always forget what my macros do. So I choose to write lots of 'green comments. Then later his helps me to remember what I did and what to do again in the future

Code:

---

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 = Ws2.Range("A" & Ws2.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 updated the macro Sir & i will use this macro
& Now problem is solved Doc Sir
any more updates & suggestions are there then plz let me know Doc Sir
& Thnx For ur Great Suggestion & for helping me in solving this problem Sir

Quote:

---

Originally Posted by fixer

....
any more updates & suggestions are there then plz let me know...

---

I would add comments. But that is just personal choice. Most people do not like to have comments.

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

---

No Problem Doc Sir
Thnx Alot for giving ur Precious Time & Providing me the Great info about this Macro Doc Sir
Problem Solved
Have a Awesome Day

Problem 1
Hi Experts,
I am looking for a macro that will do the below things

Macro will be placed in macro.xlsm(so we have to open abc by a macro)
If column E matches with Column F then delete that entire row by vba
and save the changes & close the workbook
File ABC.xls may be located anywhere in the pc, so plz hardcode the path in the macro,so that i can change it as per my needs in future

Thnx For the Help

Sample file attached below

Problem 2

Hi Experts,
I am looking for a macro that will do the below things

Macro will be placed in macro.xlsm(so we have to open abc by a macro)
If column B of DEF.xlsx matches with Column I of ABC.xls then delete that entire row
and save the changes & close the workbook
File ABC.xls & DEF.xlsx may be located anywhere in the pc, so plz hardcode the path in the macro,so that i can change it as per my needs in future

Thnx For the Help

Sample file attached below





https://www.excelforum.com/excel-pro...ml#post5365302

.....