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

[fixer]

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


Problem 2 solution has minor issue but i corrected it

' Problem 2a with VBA arrays  '  https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14645&viewfull=1#post14645
Sub SelectOutRowsNotToBeThere_SelectTheRowsIWant()  '  Problem 2         ' https://excelfox.com/forum/showthread.php/2582-delete-entire-row-by-vbA
Rem 1 Worksheet 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 WbABC = Workbooks.Open(ThisWorkbook.path & "\DEF PROBLEM 2.xlsx")  '  this line WbDEF will be there i corrected it
 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 arrIn() As Variant
' Let arrIn() = WsABC.Range("A1:K" & LrABC & "").Value2                   ' Instead of this i will use   Cells   in the   "magic  code line"
Dim arrSrch() As Variant
 Let arrSrch() = WsDEF.Range("B1:B" & LrDEF & "").Value2
Dim arrDta() As Variant
 Let arrDta() = WsABC.Range("I1:I" & LrABC & "").Value2
Rem 2 get array of indicies for wanted rows
Dim Cnt As Long
Dim strRws As String: Let strRws = "1" ' The string for the indicies will always want the first header row
    For Cnt = 2 To LrABC
    Dim MtchRes As Variant
     Let MtchRes = Application.Match(arrDta(Cnt, 1), arrSrch(), 0)
        If IsError(MtchRes) Then Let strRws = strRws & " " & Cnt ' for no match, we want to not delete this row, so do not select it out   In other words this code line collects the rows not wanted to be deleted = rows wanted
    Next Cnt
Rem 3 Get the indicies in a vertical array, since the  "magic code line"  needs a vertical array
Dim Rws() As String: Let Rws() = Split(strRws, " ", -1, vbBinaryCompare)  ' This gives us a 1 dimensional "horizontal" array  ( starting at indicie 0 )
Dim RwsT() As Variant: ReDim RwsT(1 To UBound(Rws) + 1, 1 To 1)           ' +1 is needed because the
    For Cnt = 1 To UBound(Rws) + 1
     Let RwsT(Cnt, 1) = Rws(Cnt - 1)
    Next Cnt
Rem 4 get the output array from "magic code line" :
Dim Clms() As Variant
 Let Clms() = Evaluate("=Column(A:K)")                                    ' columns  1 2 3 m4 5 6 7 8 9 10 11
Dim arrOut() As Variant
 Let arrOut() = Application.Index(WsABC.Cells, RwsT(), Clms())                       '  Magic code line            http://www.eileenslounge.com/viewtopic.php?p=265384#p265384    http://www.eileenslounge.com/viewtopic.php?p=265384&sid=39b6d764de41f462fe66f62816e5d789#p265384          See also https://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html#post4571172  , http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp   for full explanation
Rem 5 replace worksheet data with modified data arrayOut
 WsABC.Cells.ClearContents
 Let WsABC.Range("A1").Resize(UBound(arrOut(), 1), 11).Value2 = arrOut()  ' We can paste in one go the contents of an arrasy to a worksheet range
Rem Close save workbooks
 WbABC.Close Savechanges:=True                                    ' Save the file and close it
 WbDEF.Close                                                      ' Close file. No changes were made
End Sub

















































' Conventional macro for comparison: https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14646&viewfull=1#post14646