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

[DocAElstein]

Macro solution for Problem 2 ( Problem2b ) - Problem 2 https://excelfox.com/forum/showthrea...ll=1#post14648

This is a 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 )

' https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14646&viewfull=1#post14646
' Problem 2b conventional '    https://excelfox.com/forum/showthread.php/2582-delete-entire-row-by-vbA
Sub DeleteRows()
Rem 1 Worksheets data info
Dim WbABC As Workbook, WsABC As Worksheet
 Set WbABC = Workbooks.Open(ThisWorkbook.path & "\ABC.xls")
 Set WsABC = WbABC.Worksheets.Item(1)
Dim WbDEF As Workbook, WsDEF As Worksheet
 Set WbDEF = Workbooks.Open(ThisWorkbook.path & "\DEF PROBLEM 2.xlsx")
 Set WsDEF = WbABC.Worksheets.Item(1)
Dim LrABC As Long, LrDEF As Long
 Let LrABC = WsABC.Range("A" & WsABC.Rows.Count & "").End(xlUp).Row       ' Dynamically getting the last row in worksheet referenced by  WsABC
 Let LrDEF = WsDEF.Range("B" & WsDEF.Rows.Count & "").End(xlUp).Row       ' Dynamically getting the last row in worksheet referenced by  WsDEF
Dim rngSrch As Range
 Set rngSrch = WsDEF.Range("B1:B" & LrDEF & "")
Dim arrDta() As Variant
 Let arrDta() = WsABC.Range("I1:I" & LrABC & "").Value2
Rem 2 Do it
Dim Cnt
    For Cnt = LrABC To 2 Step -1
    Dim MtchedCel As Variant
     Set MtchedCel = rngSrch.Find(what:=arrDta(Cnt, 1), 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 = condition to delete
         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 ' MtchedCel  is  Nothing
         ' The attempt at a match failed, we got  Nothing  this is the condition to do nothing
        End If
    Next Cnt
Rem Close save workbooks
 WbABC.Close Savechanges:=True                                    ' Save the file and close it
 WbDEF.Close                                                      ' Close file. No changes were made
End Sub