Delete entire row

[agog12]

vba is placed in a seperate files macro.xlsm
& there are two more files 2.xlsx and 1.xlsx
all files are located in a different place
i uploaded the pic of 1.xlsx & 2.xlsx plz look sir
if column A of 1.xlsx matches with column B of 2.xlsx then keep that entire row of 2.xlsx & if not matches then delete the entire row of 2.xlsx
in this example 3rd row will be deleted of 2.xlsx
i need the macro of the same sir
so plz have a look into this problem and help me in solving this problem sir

 

[Peter_SSs]

Assuming ..
- All 3 files are open
- The data you have shown is on a sheet called 'Sheet1' in those two workbooks

.. then try this. (Make sure you have backups of your files first)

Sub DelUnwantedRows()
  With Workbooks("2.xlsx").Sheets("Sheet1")
    .Range("Z2").Formula = "=ISNA(MATCH(B2,[1.xlsx]Sheet1!A:A,0))"
    With .Range("A1").CurrentRegion
      .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("Z1:Z2"), Unique:=False
      .Offset(1).EntireRow.Delete
    End With
    On Error Resume Next
    .ShowAllData
    On Error GoTo 0
    .Range("Z2").ClearContents
  End With
End Sub

 

[agog12]

Sorry Sir my files are not opened
so i need according to that condition sir

 

[Peter_SSs]

Sorry Sir my files are not opened
so i need according to that condition sir

So, add some code at the beginning of the macro to open them? (& close them again at the end if you want)

I cannot give you the code for that because I do not know the location of the files but you should be able to turn on the macro recorder and open the files. Then you will have some code to work with.

 

[agog12]

Problem Solved Peter Sir
Thnx Alot for ur Great Help

 

[Peter_SSs]

You're welcome. Glad you were able to finish it off.