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

[DocAElstein]

macro will be placed in a separate file macro.xlsm
only macro.xlsm file is opened so we have to open rest 2 file by vba
sample1.xlsx is located at C:\Users\WolfieeeStyle\Desktop
sample2.xlsx is located at C:\Users\WolfieeeStyle\Desktop\Upstox
macro.xlsm is located at C:\Users\WolfieeeStyle\Desktop

So … in macro.xlsm means Thisworkbook.Path is C:\Users\WolfieeeStyle\Desktop

So … Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample1.xlsx") is Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")
and Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\sample2.xlsx") is Workbooks.Open(ThisWorkbook.Path & "\Upstox\sample2.xlsx")

sheet name can be anything
So , for first worksheet, use item number, Worksheets.Item(1)
( second worksheet is Worksheets.Item(2)


Before:
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1014	1030	955.5	998.45	957.4
3	NSE	ADANIPORTS	EQ	27.35	27.75	25.65	25.65	25.85
4

Worksheet: Tabelle1

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1014	1030	955.5	998.45	957.4
3	NSE	ADANIPORTS	EQ	27.35	28	29	30	27.35
4

Worksheet: Tabelle2


Run macro:

' ThisWorkbook is macro.xlsm is locatred at C:\Users\WolfieeeStyle\Desktop
'  so  ThisWorkbook.path = "C:\Users\WolfieeeStyle\Desktop"
Sub conditionally_replaceentirerow() '  http://www.excelfox.com/forum/showthread.php/2438-replace-the-entire-row
Rem 1 Worksheets info
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set Wb1 = Workbooks("sample1.xlsx")
 'Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample1.xlsx")
 'Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")
 Set Ws1 = Wb1.Worksheets.Item(1) ' worksheet of first tab
 Set Wb2 = Workbooks("sample2.xlsx") '
 'Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\sample2.xlsx")
 'Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\Upstox\sample2.xlsx")
 Set Ws2 = Wb2.Worksheets.Item(1) ' worksheet of first tab
'1b Ranges
Dim Rng1 As Range, Rng2 As Range
 Set Rng1 = Ws1.Range("A1").CurrentRegion
 Set Rng2 = Ws2.Range("A1").CurrentRegion

Rem 2 Delete an entire row in Ws2 if value in column B is not anywhere in column A of Ws1
Dim Rws As Long
    For Rws = 2 To Rng2.Rows.Count
        If Rng2.Range("H" & Rws & "").Value = Rng2.Range("D" & Rws & "").Value Then ' If column H of sample2.xlsx matches with Column D then
        Dim rngFnd As Range
         Set rngFnd = Rng1.Range("B2:B" & Rng2.Rows.Count).Find(what:=Rng2.Range("B" & Rws & "").Value, After:=Rng1.Range("B2"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)  '   https://stackoverflow.com/questions/49094391/excel-vba-range-findnext-v-range-find-what-have-i-missed/49181464#49181464
            If rngFnd Is Nothing Then ' The value from column B in Ws2 was not found in column B of Ws1
             ' do nothing
            Else
             rngFnd.Offset(0, -1).Resize(1, Rng2.Columns.Count).Copy ' B offest -1 is A  resize to all columns in range B
             Rng2.Range("A" & Rws & "").PasteSpecial Paste:=xlPasteAllUsingSourceTheme '
            End If
        Else
        ' column H of sample2.xlsx DOES NOT matches with Column D    do nothing
        End If
    Next Rws
    
'    Wb1.Save
'    Wb1.Close
'    Wb2.Save
'    Wb2.Close
End Sub

After Results

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1014	1030	955.5	998.45	957.4
3	NSE	ADANIPORTS	EQ	27.35	27.75	25.65	25.65	25.85
4

Worksheet: Tabelle2

---

sample2.xlsx : https://app.box.com/s/xavyjz9q6ek3qknam42yif4f7l0qxdfa