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

[DocAElstein]

Here is the test data : http://www.excelfox.com/forum/showth...ll=1#post11459

__ "….my all files are located in same place, "….… assume that the file with the macro in is also in this same place, ( so my macro can use ThisWorkbook.Path to return the path that I need to open the files )

__ ….."…Sheet name can be anything in both the files…"…. I will assume that the sheet is the first tab sheet. This can be accessed by its Item number property … Worksheets.Item(1) ….



__ 1 ) To get some information, I used the macro recorder…. I open the files and delete row 2 of 1.xls
I did this whilst recording a macro . This was the result:

Sub Makro2()
'
' Makro2 Makro
'

' Open Workbooks
    Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\1.xls"
    Workbooks.Open Filename:= _
        "F:\Excel0202015Jan2016\ExcelFox\vixer\BasketOrder..xlsx"
'
' As example, delete row 2 in "1.xls"
    Windows("1.xls").Activate
    
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp

End Sub

---

---

__ 2 ) The new thing for you is the VBA rng.Find Method
Range,Find Method
https://stackoverflow.com/questions/...-have-i-missed
https://docs.microsoft.com/en-us/off...cel.range.find
I look ( in code section Rem 3 ) for a cell ( as Range object ) in column C of basketorder.xlsx which matches with any cell of column B of 1.xlsx

( Important: I am then going to delete rows in a loop: We usually do such delete things in a backward loop: (Cnt = Lr2 To 1 Step -1 ). This is because I then effectively do a process on a cell or cells "behind me". So the process is done on a cell or cells no longer being considered. If I do the looping conventionally in the forward direction, then modification caused by the delete may effect the cells above, particularly their position. This can cause problems: After a delete, the cells above "move down". On the next loop I will then consider a cell above where I just was. So I will likely miss the next row to be considered, since that now occupies the position of the current loop. An alternative would be to loop forward, but after a delete to reduce the Loop count, Cnt, by 1. But changing the loop count variable in a loop is generally considered to be a bad idea ( https://www.mrexcel.com/forum/excel-...ml#post3929967 ) )

Final Code and examples here:
http://www.excelfox.com/forum/showth...ll=1#post11461
http://www.excelfox.com/forum/showth...ll=1#post11459





Alan