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

---

“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

For later use

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg. 9f6hAjkC0ct9f8jleOui-u
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eekDyfS0CD
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eevG7txd2c
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg. 9dPo-OdLmZ09dc21kigjmr
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg. 9cXui6zzkz09cZttH_-2Gf
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9iMmBDtf4m1
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxFIZ858qf7w_uA9bd4AaABAg.9dKpEpUk3YT9d VEGnka6yj
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9brzh_99JF9
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9bsrQIgXb3L
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmx0REIz41
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9bmyko2YUvQ
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmzpPqfLRD
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZwbV_Y_7UFzHwNBh4AaABAg.9dKb0Vc7MOB9d VK8si3ont
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugx6Ec_r4kb9EYOVgIt4AaABAg.9dOW613fb8V9d VIJECZIdC
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g 9cLjhPiaz
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZy1NAMBx5Uv4U2cJ4AaABAg.9f0XX-_JaGp9g9bYLMZiIy
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyL-xp8IiiahmQ12kJ4AaABAg.9f7xHCpAEx29g9asFhVFfT
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxRxyFNNp3WHTzuiJJ4AaABAg.9fFR6ECmXk69g 9afNBcS4Z
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwsdMh0FGDfvA249_B4AaABAg.9fLR6FHCIVI9g 9aLlUyzog
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g 9_4422NzK
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugwyy8JXr56HJ8m_od94AaABAg.9gSFgqqJQNV9g TXco41b5l
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TYl6RldpA
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TfhAWU9ju
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TfuYQGmUa
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g Tg3AmMPUc
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TgEqh5wdo
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxmUK0S_aZVZWz8-gt4AaABAg.9gLc3DfWfHl9gTZ3y6fL1H
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZloYeY2wQr7-xTOh4AaABAg.9gB2bbbs9mB9gTZUkNYI8e
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzlM96nGEhW9J1Gpgd4AaABAg.9fmOFVcXZh49g T_8CYeQgz
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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

Thnx Alot Doc Sir
Thnx for giving ur Precious time and Great Support to this post Sir
Thnx alot for giving ur Great Guidance Sir

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?p=303384#p303384
https://www.eileenslounge.com/viewtopic.php?p=302929#p302929
https://www.eileenslounge.com/viewtopic.php?p=302494#p302494
https://www.eileenslounge.com/viewtopic.php?p=302178#p302178
https://www.eileenslounge.com/viewtopic.php?p=301761#p301761
https://www.eileenslounge.com/viewtopic.php?p=301534#p301534
https://www.eileenslounge.com/viewtopic.php?p=301320#p301320
https://www.eileenslounge.com/viewtopic.php?p=301416#p301416
https://www.eileenslounge.com/viewtopic.php?p=301384#p301384
https://www.eileenslounge.com/viewtopic.php?p=300957#p300957
https://www.eileenslounge.com/viewtopic.php?p=300987#p300987
https://www.eileenslounge.com/viewtopic.php?f=27&t=38916&p=301354#p301354
https://www.eileenslounge.com/viewtopic.php?p=303007#p303007
https://www.eileenslounge.com/viewtopic.php?f=27&t=38910&p=301028#p301028
https://www.eileenslounge.com/viewtopic.php?p=300947#p300947
https://www.eileenslounge.com/viewtopic.php?p=300785#p300785
https://www.eileenslounge.com/viewtopic.php?p=300228#p300228
https://www.eileenslounge.com/viewtopic.php?p=300229#p300229
https://www.eileenslounge.com/viewtopic.php?p=299973#p299973
https://www.eileenslounge.com/viewtopic.php?p=299782#p299782
https://www.eileenslounge.com/viewtopic.php?f=41&t=38526&p=298390#p298390
https://www.eileenslounge.com/viewtopic.php?p=297743#p297743
https://www.eileenslounge.com/viewtopic.php?p=297638#p297638
https://www.eileenslounge.com/viewtopic.php?p=297638#p297638
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24055&viewfull=1#post24055
https://www.eileenslounge.com/viewtopic.php?p=297636#p297636
https://www.eileenslounge.com/viewtopic.php?f=27&t=38500&p=297591#p297591
https://www.eileenslounge.com/viewtopic.php?p=297326#p297326
https://www.eileenslounge.com/viewtopic.php?p=297474#p297474
https://www.eileenslounge.com/viewtopic.php?p=296754#p296754
https://www.eileenslounge.com/viewtopic.php?p=296859#p296859
https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly
https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing/page8
https://www.eileenslounge.com/viewtopic.php?p=296859#p296859
https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing?p=24006&viewfull=1#post24006
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23185&viewfull=1#post23185
https://www.eileenslounge.com/viewtopic.php?p=296344#p296344
https://www.eileenslounge.com/viewtopic.php?p=296617#p296617
https://www.eileenslounge.com/viewtopic.php?p=296145#p296145
https://www.eileenslounge.com/viewtopic.php?f=30&t=35100&p=295780#p295780
https://www.eileenslounge.com/viewtopic.php?p=310720#p310720
https://www.eileenslounge.com/viewtopic.php?p=292967#p292967
https://www.eileenslounge.com/viewtopic.php?f=55&t=37867
https://www.eileenslounge.com/viewtopic.php?p=292965#p292965
https://www.eileenslounge.com/viewtopic.php?p=292965#p292965
https://www.eileenslounge.com/viewtopic.php?p=292453#p292453
https://www.eileenslounge.com/viewtopic.php?f=26&t=37808
https://www.eileenslounge.com/viewtopic.php?p=292391#p292391
https://www.eileenslounge.com/viewtopic.php?p=247121#p247121
https://www.eileenslounge.com/viewtopic.php?f=30&t=37732&p=292181#p292181
https://www.eileenslounge.com/viewtopic.php?f=18&t=37707
https://www.eileenslounge.com/viewtopic.php?p=292019#p292019
https://www.eileenslounge.com/viewtopic.php?p=291976#p291976
https://www.eileenslounge.com/viewtopic.php?f=44&t=37709&p=291949#p291949
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg. A0opm95t2XEA0q3KshmuuY
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg. A0opm95t2XEA0q3KshmuuY
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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

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


---

Thnx Alot Doc Sir for ur Great Effort Sir
Problem Solved

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=176255#p176255
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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

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	C
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	C
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