+ Reply to Thread
Results 1 to 16 of 16

delete entire row if doesn't matches

  1. #1
    Forum Contributor
    Join Date
    06-09-2014
    MS-Off Ver
    Microst Office 365 Plus
    Posts
    143

    delete entire row if doesn't matches

    Hi
    I need a macro
    sample files has been attached below
    If column A of HOW.xlsm matches with column B of WHEN.xlsx then keep that row & if not matches then delete the entire row of WHEN.xlsx
    I will put the macro in HOW.xlsm
    WHEN.xlsx is not opened macro will open that and do the process and will save the changes made after runing the macro
    WHEN.xlsx has first row as headers so ignore that
    I will change the sheet name manually so sheet name can be anything
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: delete entire row if doesn't matches

    Maybe something like this ?

    Please Login or Register  to view this content.
    It will keep the row in WHEN.xlsx if the name in this row is in HOW.xlsm.
    The FindNext loop is in case there are two same names in different row in WHEN.xlsx
    Last edited by karmapala; 05-10-2020 at 12:03 PM.

  3. #3
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: delete entire row if doesn't matches

    How about this:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Attached Files Attached Files
    Last edited by Eastw00d; 05-10-2020 at 11:56 AM. Reason: additional information
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,159

    Re: delete entire row if doesn't matches

    If column A of HOW.xlsm matches with column B of WHEN.xlsx then keep that row & if not matches then delete the entire row of WHEN.xlsx
    Do you not mean when Col B of WHEN matches with Col A of HOW
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Cool Hi ! Try this EZ demonstration ‼


    According to the attachment as a VBA beginner starter :
    Please Login or Register  to view this content.
    ► Do you like it ? ► ► So thanks to click on bottom left star icon Add Reputation ! ◄ ◄
    Last edited by Marc L; 05-10-2020 at 12:57 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: delete entire row if doesn't matches

    Quote Originally Posted by Eastw00d View Post
    How about this:
    Hi Erwin,
    I want to learn your code, if you don't mind.
    May I ask what does this line means ?
    Please Login or Register  to view this content.
    I mean, at what condition it will give an error ?

    also, what does it mean this line ?
    Please Login or Register  to view this content.
    Thank you.
    Last edited by karmapala; 05-10-2020 at 12:18 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Hi ! Try this EZ demonstration ‼

    Quote Originally Posted by Marc L View Post
    [INDENT]
    According to the initial attachment as a VBA beginner starter :
    Wow.... ,
    those lines of code all are very new to me.
    Thank you, Marc

  8. #8
    Forum Contributor
    Join Date
    06-09-2014
    MS-Off Ver
    Microst Office 365 Plus
    Posts
    143

    Re: delete entire row if doesn't matches

    Works perfectly, thanks a lot

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Arrow Re: delete entire row if doesn't matches


    You're welcome karmapala !

    It just reproduces what any Excel beginner can achieve this just operating manually
    Last edited by Marc L; 05-10-2020 at 12:38 PM. Reason: typo

  10. #10
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: delete entire row if doesn't matches

    Quote Originally Posted by Marc L View Post

    You're welcome karmapala !

    It just reproduces what any Excel beginner can achieve this just operating manually
    Oh I see. It's interesting for me to know this new kind of code.

    BTW, at the line :
    Please Login or Register  to view this content.
    in my side, the formula only in cell F2 ?

    But if I rerun the code without closing the WHEN.xlsx...
    and I choose YES to reopen the workbook,
    then at that code line, the formula fill from F2 to F7.

    Is there any setting on Excel App I should do in order the formula fill in F2 to F7 once the workbook is open, Marc ?

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Arrow Re: delete entire row if doesn't matches

    (removed as my bad)
    Last edited by Marc L; 05-10-2020 at 12:55 PM.

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: delete entire row if doesn't matches


    My bad ! Code amended in post #5

  13. #13
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: delete entire row if doesn't matches

    Thank you, Marc. It now fill from F2 to F7 once the wb open.
    also, it's interesting, because when I just rerun the code (without closing the WHEN.xlsx), there is no prompt "do you want to reopen it ?"

    I wonder how this line of code :
    Please Login or Register  to view this content.
    can know that Sheet2.[A1].CurrentRegion.Columns(1).Address(External:=True) is in workbook "HOW.xlsm".

    So I google it with a search term : excel vba ".Address(External:=True)",
    but the result are all in planet mars language .
    Last edited by karmapala; 05-10-2020 at 01:42 PM.

  14. #14
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: delete entire row if doesn't matches

    @ karmapala: Thanks for the points

    The first on error statement will be triggered if the code after this produces an error, in this case, the "MATCH" gives an error, then it will go to the line with the word "EraseLine:" and resumes executing code after this.
    The "On Error GoTo -1" resets the error code, so when resuming the procedure, it is open for triggering again in the next round.

    Cheers
    Erwin

  15. #15
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: delete entire row if doesn't matches

    Quote Originally Posted by Eastw00d View Post
    @ karmapala: Thanks for the points
    Thanks also from me

    The first on error statement will be triggered if the code after this produces an error, in this case,
    the "MATCH" gives an error
    Oh i understand this now.


    then it will go to the line with the word "EraseLine:" and resumes executing code after this.
    The "On Error GoTo -1" resets the error code, so when resuming the procedure,
    it is open for triggering again in the next round.
    The bold also new things for me.

    Thank you, Erwin.

  16. #16
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Arrow Re: delete entire row if doesn't matches

    Quote Originally Posted by karmapala View Post
    I wonder how this line of code :
    Please Login or Register  to view this content.
    can know that Sheet2.[A1].CurrentRegion.Columns(1).Address(External:=True) is in workbook "HOW.xlsm".

    So I google it with a search term : excel vba ".Address(External:=True)",
    but the result are all in planet mars language .
    Rather than the poor Google just check in the VBA help as all it there !

    Address statement with its External parameter returns the complete path of the data aka Book+Sheet+Range Address

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. RemoveDuplicates doesn't seem to delete the entire row
    By mainemojo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2020, 08:32 AM
  2. Delete Entire Row on Every Sheet if Date Condition Matches
    By c052463 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2020, 12:53 PM
  3. [SOLVED] Macro Needed to Delete Entire Row if the Entire Row (Or Col A,B,C,D in row ) are blank
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-04-2016, 06:32 AM
  4. [SOLVED] delete entire row if it doesn't contains at least 1 of the words listed
    By 4evra in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-30-2013, 02:14 PM
  5. Delete entire row if column a matches column d
    By martinez_pedro in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-16-2013, 04:09 PM
  6. Replies: 7
    Last Post: 10-23-2012, 08:38 AM
  7. Match 1 criteria if matches then copy the entire row plus 1 row above and below
    By khalid79m in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-27-2007, 11:32 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1