+ Reply to Thread
Results 1 to 9 of 9

delete entire row with conditions

  1. #1
    Registered User
    Join Date
    03-22-2018
    Location
    Melbourne
    MS-Off Ver
    Ms office 2016
    Posts
    18

    Question delete entire row with conditions

    Hi Guys

    I use excel 2010

    Need your help on this.

    I have got source data which comes like this:


    sales broker
    2000 smith
    3000 john
    4000 mike
    5000 julie
    6000 belinda
    7000 john
    8000 smith

    I want to delete all the rows where broker name is smith or john, and keep all the other rows .

    data updates everyday, what will be the best option to create any macro to do this or is there any option in power query
    to achieve this.

    please advise both solutions with vba or macro and any option with the power query in excel 2010.

    thanks

    bondtk
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: delete entire row with conditions

    Attached could be one way.
    It works on a selection from a UserForm Listbox.
    Attached Files Attached Files

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: delete entire row with conditions

    Post deleted as jolivanes solution is much better and gives user more options.

    Alf
    Last edited by Alf; 11-16-2019 at 02:49 AM.

  4. #4
    Registered User
    Join Date
    03-22-2018
    Location
    Melbourne
    MS-Off Ver
    Ms office 2016
    Posts
    18

    Re: delete entire row with conditions

    Hi Jolivanes

    Thanks for your prompt answer, its a good solution, I am just wondering , is it possible that it can delete the rows without asking for the pop up box, for example is it possible to built in the list of brokers which I don't want in the macro and when you run the macro it just deletes automatically every time data updates.
    please advise.

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: delete entire row with conditions

    I think that this is what Alf had but he/she unfortunately deleted it thinking that the other solution had more possibilities in case names to be deleted were changed.
    Please Login or Register  to view this content.
    Last edited by jolivanes; 11-16-2019 at 11:44 AM. Reason: fix spelling

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: delete entire row with conditions

    Well jolivane I still do think your solution is the best one but her is what I wrote:

    Please Login or Register  to view this content.
    So as you see they are both almost identical. I also added a comment about using input box for up to two values (as you have shown) if more than two one could still use and input box but then the value had to be read into an array

    Alf

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: delete entire row with conditions

    Another code
    Please Login or Register  to view this content.
    - Battle without fear gives no glory - Just try

  8. #8
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: delete entire row with conditions

    Here is one more macro to consider...
    Please Login or Register  to view this content.
    Note: Put the brokers whose rows you want to delete as a quoted comma delimited list where I have highlighted in red.

  9. #9
    Registered User
    Join Date
    03-22-2018
    Location
    Melbourne
    MS-Off Ver
    Ms office 2016
    Posts
    18

    Re: delete entire row with conditions

    Thanks Rick , it works for me.

+ 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. [SOLVED] Loop to Delete Entire.Rows if conditions are met
    By stein7 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-29-2018, 09:01 AM
  2. [SOLVED] Help combining macros to delete rows on conditions and add data into cells on conditions
    By JayJayGC in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-03-2017, 10:14 AM
  3. iCell.EntireRow.Delete not working. Need code to Delete Entire Row
    By unit285 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 05-27-2017, 10:48 PM
  4. [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
  5. [SOLVED] Extract entire row with 3 conditions.
    By Sekars in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-21-2016, 03:23 AM
  6. VBA code to delete the FILTERED range and moving the cell up (dont Delete entire row)
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-14-2015, 07:48 AM
  7. [SOLVED] Delete cell only with certain critria. Don't want to delete entire row, the celsl only
    By RobertOHare in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-16-2013, 04:18 AM

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