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

Code:

---

Sub conditionally_delete()
On Error Resume Next
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\STEP1U.xlsb")
 Set Ws1 = Wb1.Worksheets.Item("Sheet1") ' worksheet with string tab name of  Sheet1
 Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
 Set Ws2 = Wb2.Worksheets.Item(1) ' worksheet of first tab
'1b Ranges
Dim Rng1A As Range, Rng2B 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
   
    Wb1.Save
    Wb1.Close
    Wb2.Save
    Wb2.Close
   
End Sub

---

I am getting error with this line Doc Sir

Code:

---

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 & "")

---

plz have a relook Doc Sir

You should be able to see a possible problem ...
Where is your Rng1 and Rng1 defined????
You must have Rng1 and Rng1...
You are missing this

Code:

---

Dim Rng1 As Range, Rng2 As Range
 Set Rng1 = Ws1.Range("A1").CurrentRegion: Set Rng2 = Ws2.Range("A1").CurrentRegion

---

Missing Rng1 Rng2.JPG : https://imgur.com/pF9JLdC
Attachment 2807

Code:

---

'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, 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 ..............................

---

---

( Note also : It is very bad programming to use On Error Resume Next at the start of a program )

May i know the reason why it is bad Doc sir So that i can be safe with my work
I have not use this in every code but Now some modification is needed and as per requirement that line is needed but may i know the information Doc sir why i should not use that & what that line will do, it will only stop the Error msg only correct me doc Sir if i am wrong

On Error Resume Next is one of a few ways to do “Error handling in VBA” ( http://www.excelfox.com/forum/showth...GoRoT-N0Nula-1 )

On Error Resume Next at the start of your program will tell VBA to keep going even if it crashes or has problems many times.
It is like you go on a long journey in your car. But you decide to drive very fast and if you have lots of crashes and accidents you just keep going very fast and ignore all your accidents and crashes.
This can do damage and break things.

It does not just stop the error message. It does stop the error message , you are correct , … but also it does keep going even if things are broken or not working properly. This might do bad damage. You may not notice this damage until something else goes badly wrong. It may cause lots of confusing problems later.

On Error Resume Next at the start of your program is dangerous. You may be lucky one day and it will cause no problems. Or you may be unlucky and it will cause lots of problems and cause lots of damage.


Usually there is a better way to achieve what you want without Error Handling.


Some ways to use Error handling are not too bad.
For example:

This is very Bad

Code:

---

Sub BadErrorHandling()
On Error Resume Next ' Error handling is ON
Dim Nmbr As Long: Let Nmbr = 0

'
'  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
'

 Let Nmbr = 1 / Nmbr

 '
 '
 '  code
''  code
''  code
''  code
''  code
''  code
'
End Sub

---

This is not so bad

Code:

---

Sub NotSoBadErrorHandling()

Dim Nmbr As Long: Let Nmbr = 0

'
'  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
'
On Error Resume Next ' Error handling is ON
 Let Nmbr = 1 / Nmbr
On Error GoTo 0      ' Error handling is OFF
 '
 '
 '  code
''  code
''  code
''  code
''  code
''  code
'
End Sub

---

The last macro is not so bad because you only use the error handling for a short piece of the code which you think may error.

But it is still better to do it without error handling

Code:

---

Sub AlternativeToErrorHandling()

Dim Nmbr As Long: Let Nmbr = 0

'
'  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
'

    If Nmbr <> 0 Then Let Nmbr = 1 / Nmbr

 '
 '
 '  code
''  code
''  code
''  code
''  code
''  code
'
End Sub

---

Alan

Code:

---

Sub conditionally_delete()
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\STEP1U.xlsb")
 Set Ws1 = Wb1.Worksheets.Item("Sheet1") ' worksheet with string tab name of  Sheet1
 Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
 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, Rng2B 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
   
    Wb1.Save
    Wb1.Close
    Wb2.Save
    Wb2.Close
   
End Sub

---

Doc Sir i used this code but i am not getting desired result i think i have putted something incorrect plz have a look Doc Sir

No issue Doc Sir i have another plan for this i will not use that line Thnx for the guidance Sir but i am not getting the desired result plz look Sir

The last macro of yours ( http://www.excelfox.com/forum/showth...ll=1#post12882 ) looks OK

So do two things

_1) Upload a small sample data of
STEP1U.xlsb
And
1.xls


and

_2) tell me what is not doing what you want: Tell me what result is wrong

plz see the sample file Doc Sir i tried to explain the condition in the sample file doc sir

vba will be placed in a seperate file

Before:- ( from your uploaded files )
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D
1
2	ACC	150
3	DLF	120
4
5			match column A of sample1.xlsx with column B of sample2.xlsx
6			If it matches then do nohing
7			if it doesn’t matches then delete that enire row
8

Worksheet: Sheet1

_____ 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	1034	1068.75	988	998.45	998.45
3	NSE	ADANIENT	EQ	123.05	135	118.2	120.9	120.9	This entire row will be deleted
4	NSE	DLF	EQ	235	235.8	203	207.8	207.8
5

Worksheet: Sheet1

Now I run this

Code:

---

Sub conditionally_delete()
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set Wb1 = Workbooks("sample1.xlsx") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\STEP1U.xlsb")
 Set Ws1 = Wb1.Worksheets.Item("Sheet1") ' worksheet with string tab name of  Sheet1
 Set Wb2 = Workbooks("sample2.xlsx") '  Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
 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, Rng2B 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
'    Wb1.Save
'    Wb1.Close
'    Wb2.Save
'    Wb2.Close
End Sub

---

After run:-

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

Row\Col	A	B	C
1
2	ACC	150
3	DLF	120
4

Worksheet: Sheet1

_____ 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	1034	1068.75	988	998.45	998.45
3	NSE	DLF	EQ	235	235.8	203	207.8	207.8
4
5

Worksheet: Sheet1


I see no problem...
This is almost a repeat of previous posts , with similar data files and results ... nothing much new here...

I uploaded my real file plz have a look bcoz i am runing the code and i am not getting any result
Plz have a look Doc Sir

Same results again....

( I assume that STEP1U.xlsb is like previous 1.xlsx or sample1.xlsx
and
I assume 1.xls is like previous sample2.xlsx and 2.xlsx )

Before:-

_____ Workbook: STEP1U.xlsb ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1
2	ACC	100
3	DLF	150
4

Worksheet: Sheet1

_____ Workbook: 1.xls ( 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	ADANIPOWER	EQ	27.35	27.75	25.65	25.65	25.85
4	NSE	DLF	EQ	390	419.7	350.05	387.25	352.4
5	NSE	AMBUJACEM	EQ	145.1	155.8	142.55	145.85	145
6	NSE	APOLLOHOSP	EQ	1250	1265	1124.15	1158.65	1127.05
7

Worksheet: 1-Sheet1


Same macro as before ( http://www.excelfox.com/forum/showth...ll=1#post12887 )
Run macro ...

Code:

---

Sub conditionally_delete() '  http://www.excelfox.com/forum/showthread.php/2436-conditionally-delete-entire-row?p=12890&viewfull=1#post12890
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set Wb1 = Workbooks("STEP1U.xlsb") ' Workbooks("sample1.xlsx") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\STEP1U.xlsb")
 Set Ws1 = Wb1.Worksheets.Item("Sheet1") ' worksheet with string tab name of  Sheet1
 Set Wb2 = Workbooks("1.xls") ' Workbooks("sample2.xlsx") '  Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
 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, Rng2B 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
'    Wb1.Save
'    Wb1.Close
'    Wb2.Save
'    Wb2.Close
End Sub

---

After run macro

_____ Workbook: STEP1U.xlsb ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1
2	ACC	100
3	DLF	150
4

Worksheet: Sheet1

_____ Workbook: 1.xls ( 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	DLF	EQ	390	419.7	350.05	387.25	352.4
4	NSE	AMBUJACEM	EQ	145.1	155.8	142.55	145.85	145
5	NSE	APOLLOHOSP	EQ	1250	1265	1124.15	1158.65	1127.05
6

Worksheet: 1-Sheet1


Same results with similar data files.
Third attempt at doing almost the same thing.
No major difference in data or results.

Results are correct, or at least they all do what you asked for....
ACC column A in STEP1U.xlsb was equal to ACC column B in 1.xls then keep that entire row
DLF column A in STEP1U.xlsb was not equal to ADANIPOWER column B in 1.xls , so row 3 in 1.xls , entire row, is deleted.
.........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......

Now i understood what is the issue
Doc Sir everything will be deleted in this sheet see the sample pic only the row that has ACC & dLf that row will be there and rest will be deleted
see the sample pic and delete rest data except acc and dlf

You must try to understand and explain what you want.

These two are a contradiction:
You say before:
If column A of STEP1U.xls matches with column B of 1.xls then keep that entire row of 1.xls & if not matches then delete the entire row of 1.xls

Now you say…. only the row that has ACC & dLf that row will be there …

. But

in row 3 we have
DLF column A __ ADANIPOWER column B

In row 4 we have
Column A empty __ DLF in column B

So in those rows are no match = not matches

DLF column A <> ADANIPOWER column B
Column A empty <> DLF in column B

Either
You do not know yourself what you want
Or
You are totally unable to explain what you want.


It is impossible to help you if you can not explain what it is you want.
Either try again to explain, or give better data to show what you want


As always, the problem is not the macro. The macros always do what you ask for. But you always ask wrong.
God may know what you want. But I am not God. :)
If you are unable to explain , then at least give much better data to show what you want.

How about this….

What is wanted….
….. I have two workbooks, STEP1U.xlsb and 1.xls
There is a data range in the first worksheet of both workbooks, from rows 2. ( I have a header range in rows 1 )

Compare/ Consider column A in the first worksheet of STEP1U.xlsb with column B in the first worksheet of 1.xls. ( Ignore the first row – this is the header row )

If the value in column B in the first worksheet of 1.xls is not in column A in the first worksheet of STEP1U.xlsb , then the entire row , ( in 1.xls ), containing that value is to be deleted.
( STEP1U.xlsb is not changed )

Alternative short explanation: Delete all the rows in 1.xls whose value in column B is not to be found in column A in STEP1U.xlsb. ( STEP1U.xlsb remains unchanged )


Before:-
_____ Workbook: STEP1U.xlsb ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1
2	ACC	100
3	DLF	150
4

Worksheet: Sheet1

_____ Workbook: 1.xls ( 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	ADANIPOWER	EQ	27.35	27.75	25.65	25.65	25.85
4	NSE	DLF	EQ	390	419.7	350.05	387.25	352.4
5	NSE	AMBUJACEM	EQ	145.1	155.8	142.55	145.85	145
6	NSE	APOLLOHOSP	EQ	1250	1265	1124.15	1158.65	1127.05
7

Worksheet: 1-Sheet1


After :-

_____ Workbook: STEP1U.xlsb ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1
2	ACC	100
3	DLF	150
4

Worksheet: Sheet1

_____ Workbook: 1.xls ( 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	DLF	EQ	390	419.7	350.05	387.25	352.4
4
5

Worksheet: 1-Sheet1

---

Solution:-

See here:
http://www.excelfox.com/forum/showth...ll=1#post12896

---

See also some Important notes on looping backwards when deleting rows
http://www.excelfox.com/forum/showth...ll=1#post12902

Sorry Doc Sir for a mistake
Thnx Alot Doc Sir for ur Great effort & Great help
Problem Solved

---

Moderator Translation:
I have managed to totally confuse myself and everyome else by posting gibberish that I don’t even understand myself, so lets just chuck in one of my canned replies, ( that I don’t understand either ) , and just get out of here…
The a few days ler after multiple cross duplicated poostings I am a total fucking idiot dim pig shit for brains, so just give me a macro that does what I want, even though most of the time I aint got a fucking clue myself what I want because I am such a total fucking spacko ... and never mind anyway, fuck off you mug, as some other twat gave me a macro at one of my many duplicated cross postings.......( Intro to Avinash Singh... https://excelfox.com/forum/showthrea...h-Introduction )

there are two files file name sample1.xlsx & sample2.xlsx
vba will be placed in a seperate file macro.xlsm
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
only macro.xlsm file is opened so we have to open rest 2 file by vba
If column H of sample2.xlsx matches with Column D then look column B data of sample2.xlsx and find that data in sample1.xlsx in column B and after getting that data in sample1.xlsx in column B , copy that entire row of sample1.xlsx and paste that in sample2.xlsx in the same row
all this is have to do by vba
so plz have a look into this problem and help me in solving this problem sir
sheet name can be anything

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\sam ple1.xlsx") is Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")
and Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Ups tox\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:

Code:

---

' 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

Awesome Doc Sir Thnx Alot for ur Great Effort Doc Sir
Problem Solved

Hi, I am looking for a macro that will do the things as mentioned below

If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls
If column J of 1.xls has a blank cell then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls
If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xls then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls



These are the three condition that macro will follow and based on that it will work
all files are located in different path, macro will be placed in separte file macro.xlsm
sheet name can be anything

https://www.excelforum.com/excel-pro...ml#post5340172
This link has solved the solution by considering alert.csv but now it is changed to alert.xls

Quote:

---

Originally Posted by fixer

...https://www.excelforum.com/excel-pro...ml#post5340172
This link has solved the solution by considering alert.csv but now it is changed to alert.xls

---

The solution here, https://www.excelforum.com/excel-pro...ml#post5340172 , was optimised for using a text file as Alert.csv , using advanced Text file data manipulation techniques
There is no useful coding information there which can be applied to the current requirement : The macro from jindon is no use in the current question here.

---

If column J of 1.xls has BUY & column H of 1.xls is not greater than column D of 1.xls then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls
If column J of 1.xls has a blank cell then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls
If column J of 1.xls has SHORT & column H of 1.xls is Greater than column D of 1.xls then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls
row 3 & row 4 & row 5 will be deleted after running the macro

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

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1183.72	1240	1161.6	1227.1	1227.1	22	BUY
3	NSE	ADANIENT	EQ	139.38	141.2	136.6	138.1	140	25	BUY
4	NSE	ADANIPORTS	EQ	311.85	315	306.55	310.6	312	15083	SHORT
5	NSE	ADANIPOWER	EQ	33.165	34.5	32.85	33	33.2	17388	SHORT
6	NSE	AMARAJABAT	EQ	594	613.5	586.9	592.55	594	100
7	NSE	ASIANPAINT	EQ	1584.488	1625	1555.4	1617.9	1617.9	236	BUY

Worksheet: 1-Sheet1 9 June

_____ Workbook: Alert.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	NSE	236	6	>	431555	A					GTT
2	NSE	25	6	>	431555	A					GTT
3	NSE	15083	6	>	431555	A					GTT
4	NSE	17388	6	>	431555	A					GTT
5	NSE	100	6	>	431555	A					GTT
6	NSE	22	6	>	431555	A					GTT

Worksheet: Alert 9 June xls

After running macro here: https://excelfox.com/forum/showthrea...ll=1#post13733


_____ Workbook: Alert.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	NSE	236	6	>	431555	A					GTT
2	NSE	25	6	>	431555	A					GTT
3	NSE	22	6	>	431555	A					GTT
4
5
6

Worksheet: Alert 9 June xls



Alan

---

macro.xlsm : https://app.box.com/s/z358r7tbc9hzthi539dlj49jsf4gyg8p
1.xls : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk
Alert.xls : https://app.box.com/s/ectstkrcfnuozys9tmdd0qi3tdvyxb3w

Problem Solved
Thnx Alot Doc Sir for helping me in solving this problem Sir

This was the Macro

Code:

---

Sub STEP3()
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
    For Cnt = Lr2 To 1 Step -1
    Dim MtchedCel As Variant
    Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then
       
        Else
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        End If
       
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub

---

I changed this Macro As per my needs but getting error
So Plz have a look Sir

Code:

---

Sub STEP6()
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
    For Cnt = Lr2 To 1 Step -1
    Dim MtchedCel As Variant
    Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        Else
       
        End If
       
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub

---

Plz see the else statement in both the macros

Error which i got, I uploaded the pic of the same




https://eileenslounge.com/viewtopic....271392#p271392



Plz run the macro the file which i have uploaded

We are back to about a year ago, around here
https://excelfox.com/forum/showthrea...ll=1#post11467
https://excelfox.com/forum/showthrea...ll=1#post12896
https://excelfox.com/forum/showthrea...ll=1#post12897

---

Quote:

---

Originally Posted by fixer

problem solved .. please delete Thread

---

No. Problem is not solved
I still see at least two issues

Problem 1
You still do not understand Lr issue. You still have it wrong….
Lr variable, we have typically used in many of your macros , ( in the meantime well over a hundred similar macros ) , to determine dynamically the last used cell in a column.

For example, in your macro, for the first column in the two worksheets :-
Lr1 is for last row dynamically found for worksheet, Ws1
Lr2 is for last row dynamically found for worksheet, Ws2

So it should be

Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row

Problem 2

Code:

---

        If Not MtchedCel Is Nothing Then
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        Else
       
        End If

---

Or

Code:

---

        If Not MtchedCel Is Nothing Then
       
        Else
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        End If

---

You always get that mixed up. Problem is English language and general idiocy. You may never know what you want. Half the time you will get it correct. The other half of the time you will get it wrong.

---

Most of the time it continues to appear as if you either do not read or understand anything that anyone anywhere ever writes for you.
You continue to go around in chaotic circles of multiple duplicate cross posting of the same or very similar issues, and seem to be starting again recently, with even more cross posting of very similar questions.
Because of this you are now experiencing even more problems: Everyone has a different posting style, and there are usually many ways to solve a problem in VBA. So you are producing a chaotic collection of coding written by many different people, in many different ways. This collection of coding, some working perfectly, some not erroring, but not working properly, you will never, ever, be able to combine to achieve anything over than a total chaotic mess….

Problem 1)

Code:

---

Sub STEP3()
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
    For Cnt = Lr2 To 1 Step -1
    Dim MtchedCel As Variant
    Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then
       
        Else
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        End If
       
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub

---

R u talking about this Doc Sir
I have used this macro
Do u mean to say that
I have to use this in this macro

Code:

---

Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row

---

Problem 2

https://eileenslounge.com/viewtopic.php?f=30&t=34937

U r talking about this
so may i know what changes is required in the second problem so that i can put the same & be safe




3)Thnx Bro for all of ur Great Efforts, this is Best thing about u
Thnx for knowing me about the problems
& Yes this question i asked u in last year , & it is working fine
I just want to make the system Awesome Like u Bro
U r the one of the best person i ever met
Thnx for keeping a eye into all of my problems that i may have infuture

Problem 1)

Quote:

---

Originally Posted by fixer

Problem 1)
Do u mean to say that
I have to use this in this macro

Code:

---

Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row

---

..

---

Yes

---

Problem 2)
This is not a VBA problem.
You cant speak English or understand any simple thing in English.
I am not sure what the solution to such problems are.
Possibly try to leave in some 'green comments so then later you will remember what code line is doing. Then later it is easier to remember and later it is easier to see where is problem
'green comments do not cause any problems
'green comments do not make any coding slower
There will never be issues caused by 'green comments
'green comments can stay for ever. They do no harm
VBA does not see 'green comments. So VBA is not effected by 'green comments For VBA , 'green comments are invisible. So 'green comments cannot have any effect on any coding
There will never be any problems caused by 'green comments
There can never be any problems caused by 'green comments
A code line can be very long. So you can have a very long ling full of important information so that later you can see what all code line are doing.
So if you have lots of 'green comments you will not need to remember, because all information is there for you to read later if you need to know again what is the code doing.

But everyone has a different choice. You can choose

Some people use no 'green comments
Some people use some 'green comments
Some people use lots of 'green comments
I use very many 'green comments because I am not smart. I am stupid. I forget everything. I am not a computer programmer professional. I am a stupid Builder. I always forget what my macros do. So I choose to write lots of 'green comments. Then later his helps me to remember what I did and what to do again in the future

Code:

---

Sub STEP3()
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
    For Cnt = Lr2 To 1 Step -1
    Dim MtchedCel As Variant
    Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then
       
        Else
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
        End If
       
    Next Cnt
 Wb1.Close SaveChanges:=True
 Wb2.Close SaveChanges:=True
End Sub

---

I updated the macro Sir & i will use this macro
& Now problem is solved Doc Sir
any more updates & suggestions are there then plz let me know Doc Sir
& Thnx For ur Great Suggestion & for helping me in solving this problem Sir

Quote:

---

Originally Posted by fixer

....
any more updates & suggestions are there then plz let me know...

---

I would add comments. But that is just personal choice. Most people do not like to have comments.

Code:

---

Sub STEP3() '  https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14587&viewfull=1#post14587    https://eileenslounge.com/viewtopic.php?f=30&t=34937
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)                                  ' First worksheet tab counting from the left
Set Ws2 = Wb2.Worksheets.Item(2)                                  ' Second worksheet tab cunting from the left
Dim Lr1 As Long, Lr2 As Long
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row      ' Dynamically getting the last row in worksheet referenced by  Ws1
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row      ' Dynamically getting the last row in worksheet referenced by  Ws2
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")  ' The range that will be searched in
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")    ' The range from which data will be looked for in rngSrch

Dim Cnt As Long  '    For  each  rngDta.Item(Cnt)
    For Cnt = Lr2 To 1 Step -1                                    ' We take  -ve  steps = we go backwards. This is important when deleteing things.  See: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12902&viewfull=1#post12902
    Dim MtchedCel As Variant
    Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        If Not MtchedCel Is Nothing Then ' Range.Find  would return nothing if it did not find a match
              ' If it was  Not  Nothing  then there was a match. So we do nothing
        Else  ' The attempt at a match failed, we got  Nothing  this is the condition to delete
        rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp            ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet  Up
        End If
       
    Next Cnt            ' Next  rngDta.Item(Cnt)
 Wb1.Close SaveChanges:=True                                      ' Save the file and close it
 Wb2.Close SaveChanges:=True                                      ' Save the file and close it
End Sub

---

No Problem Doc Sir
Thnx Alot for giving ur Precious Time & Providing me the Great info about this Macro Doc Sir
Problem Solved
Have a Awesome Day

Problem 1
Hi Experts,
I am looking for a macro that will do the below things

Macro will be placed in macro.xlsm(so we have to open abc by a macro)
If column E matches with Column F then delete that entire row by vba
and save the changes & close the workbook
File ABC.xls may be located anywhere in the pc, so plz hardcode the path in the macro,so that i can change it as per my needs in future

Thnx For the Help

Sample file attached below

Problem 2

Hi Experts,
I am looking for a macro that will do the below things

Macro will be placed in macro.xlsm(so we have to open abc by a macro)
If column B of DEF.xlsx matches with Column I of ABC.xls then delete that entire row
and save the changes & close the workbook
File ABC.xls & DEF.xlsx may be located anywhere in the pc, so plz hardcode the path in the macro,so that i can change it as per my needs in future

Thnx For the Help

Sample file attached below





https://www.excelforum.com/excel-pro...ml#post5365302

.....