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

**fixer** · 09-03-2019, 11:42 AM

“Moderator” Notice
September 2020
Given up with this “OP” , Avinash around September 2020. https://excelfox.com/forum/showthrea...ll=1#post14972
https://excelfox.com/forum/showthrea...ll=1#post14972
I am no longer monitoring what its doings. It had curiosity appeal for a while, but even that has worn off me now!
It’s getting worse by the Day. Its still doing whatever it is that it is doing and getting Replies and answers at excelforum.com and likely a few places I don’t know about.

I will place the vba code in Report.xlsm
my all files are located in same place
Sheet name can be anything in both the files
I need the vba code that will open both the file and do the process and save the file
If cells of column C of basketorder.xlsx matches with cells of column B of 1.xlsx then delete the entire row of 1.xlsx(here entire row means the cells which matches delete that entire row)
and after the process close and save the file so that changes should be saved

http://www.vbaexpress.com/forum/show...811-Delete-row

**DocAElstein** · 09-04-2019, 03:29 PM

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:

Code:

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

**fixer** · 09-04-2019, 07:20 PM

**DocAElstein** · 09-05-2019, 03:22 PM

Here is some extra info on making Lr dynamic:
http://www.excelfox.com/forum/showth...ll=1#post11466
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466

Alan

**fixer** · 09-06-2019, 09:26 PM

Thnx Doc Sir for the info
Thnx Alot Sir
Have a Great Day

**fixer** · 03-22-2020, 09:48 PM

vba is placed in a sepearte file macro.xlsm
and there are two more files 1.xlsx & 2.xlsx
all files are located in different path
i am looking to slve this problem by vba macro
if column A of 1.xlsx matches with column A of of 2.xlsx then calculate the data in that row and if the number is positive then delete all the numbers from that entire row (numbers is starting from column B) & if it is negative then do nothing
example in current case acc matches dlf matches and tata matches
acc is negative (40-50=-10) then do nothing
dlf is positive (10+200=210) then delete the numbers which is starting from column B i.e 10 & 200
and so on
only macro.xlsm is open so we have to open that file

**DocAElstein** · 03-23-2020, 04:17 AM

Try this

Code:

Sub conditionally_delete_entire_row() ' http://www.excelfox.com/forum/showthread.php/2436-conditionally-delete-entire-row
Rem 1  workbooks, Worksheets info  '  only macro.xlsm is open so we have to open that file
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
Workbooks.Open Filename:=ThisWorkbook.Path & "\1.xlsx"
 Set Wb1 = ActiveWorkbook
 Set Ws1 = Wb1.Worksheets.Item("Sheet1") ' worksheet with string tab name of  Sheet1
 Set Wb2 = Workbooks.Open(Filename:=ThisWorkbook.Path & "\2.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
Dim Rng1A As Range, Rng2A As Range
 Set Rng1A = Rng1.Range("A1:A" & Rng1.Rows.Count & ""): Set Rng2A = Rng2.Range("A1:A" & Rng2.Rows.Count & "")
Rem 2 take each cell in column A of 1.xlsx and compare it with  each cell in column A of of 2.xlsx
Dim RngS1 As Range, RngS2 As Range
    For Each RngS1 In Rng1A ' each cell in column A of 1
        For Each RngS2 In Rng2A ' each cell in column A of 2
            If RngS1.Value = RngS2.Value Then ' compare .. if column A of 1.xlsx matches with column A of of 2.xlsx
            Dim DtaClc As Long ' calculate the data in that row
             Let DtaClc = Application.WorksheetFunction.Sum(RngS2.Resize(1, Rng2.Columns.Count)) ' https://docs.microsoft.com/de-de/office/vba/api/excel.worksheetfunction.sum
                If DtaClc > 0 Then ' delete all the numbers from that entire row (numbers is starting from column B)
                 'RngS2.Offset(0, 1).Resize(1, Rng2.Columns.Count - 1).Delete shift:=xlToLeft
                 RngS2.Offset(0, 1).Resize(1, Rng2.Columns.Count - 1).ClearContents
                Else
                ' Dtaclc  is negative then do nothing
                End If
            Else
            ' column A of 1.xlsx does not match with column A of of 2.xlsx -  do nothing
            End If
        Next RngS2
    Next RngS1
End Sub

**fixer** · 03-23-2020, 10:39 AM

Thnx Alot Doc Sir for ur Great Effort Sir
Problem Solved

**fixer** · 03-23-2020, 10:44 AM

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
plz see the sample file

**DocAElstein** · 03-23-2020, 02:57 PM

Change to this

Code:

Dim Rng1A As Range, Rng2B As Range ' , Rng2A As Range
 Set Rng1A = Rng1.Range("A1:A" & Rng1.Rows.Count & ""): Set Rng2B = Rng2.Range("B1:B" & Rng2.Rows.Count & "")  ' : Set Rng2A = Rng2.Range("A1:A" & Rng2.Rows.Count & "")
Rem 2 take each row in column A of 1.xlsx and compare it with  each row in column B of of 2.xlsx
Dim Rws As Long
    For Rws = Rng1.Rows.Count To 2 Step -1
        If Rng1A.Item(Rws).Value = Rng2B.Item(Rws).Value Then
        ' Do nothing
        Else
         Rng2B.Item(Rws).EntireRow.Delete Shift:=xlUp
        End If
    Next Rws
End Sub

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

_Row\^Col	A	B
1
2	ACC	6
3	DLF	4
4

Worksheet: Sheet1

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

_Row\^Col	A	B	C	D	E	F	G
1	Exchange
2	NSE	ACC	EQ	10	11	12	13
3	NSE	ADANIENT	EQ	8	7	6	5
4	NSE	DLF	EQ	1	2	3	4
5

Worksheet: Sheet1

After:

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

_Row\^Col	A	B
1
2	ACC	6
3	DLF	4
4

Worksheet: Sheet1

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

_Row\^Col	A	B	C	D	E	F
1	Exchange
2	NSE	ACC	EQ	10	11	12
3	NSE	DLF	EQ	1	2	3
4
5

Worksheet: Sheet1

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

Thread Tools

Search Thread

Display

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

conditionally delete or replace entire row

conditionally delete or replace entire row

Similar Threads

Delete rows based on match criteria in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos

Unique Concatenate Range, multiple rows, Index Match Mutliple Criteria

VBA To Delete Rows Based On Value Criteria In A Column

Sort Data By Multiple Columns And Delete Some Rows Based On Criteria

Delete Remove Rows By Criteria VBA Excel

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions