Appendix Thread. ( Codes for other Threads, ( Avinash ).)

[DocAElstein]

Macros for this Post
https://excelfox.com/forum/showthrea...ll=1#post14639


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

Sub STEP3c() '  https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14639&viewfull=1#post14639
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\1(sample).xls")                ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\H2SAMPLE.xlsx") ' 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 counting from the left
Dim Lr1 As Long, Lr2 As Long
Let Lr1 = Ws1.Range("B" & 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("A1:A" & Lr2 & "")   ' The range that will be searched in
Dim rngDta As Range: Set rngDta = Ws1.Range("B1:B" & Lr1 & "")     ' The range from which data will be looked for in rngSrch

Dim Cnt As Long  '    For  each  rngDta.Item(Cnt)
    For Cnt = Lr1 To 2 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.  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
    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

Or

Sub STEP3d() ' https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14639&viewfull=1#post14639  https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14639&viewfull=1#post14639
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\1(sample).xls")                ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\H2SAMPLE.xlsx") ' 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("B" & 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("A1:A" & Lr2 & "")   ' The range that will be searched in
Dim rngDta As Range: Set rngDta = Ws1.Range("B1:B" & Lr1 & "")     ' The range from which data will be looked for in rngSrch

Dim Cnt As Long  '    For  each  rngDta.Item(Cnt)
    For Cnt = Lr1 To 2 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 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
    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