+ Reply to Thread
Results 1 to 22 of 22

select rows simulataneously that contain a specific value

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    select rows simulataneously that contain a specific value

    is there an option or vba code to select cells that contain a specific values and delete entire rows? for example, I want to search for a customer, and delete their entire row (for example, a customer bought 5 things, and it shows on the logs, i want to delete this 5 rows, and the next customer bought 8 things, I want to be able to select this 8 rows and delete) I saw something like that in kutools, but need it more personalize, as for a prompt box and enter data)

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: select rows simulataneously that contain a specific value

    This is possible, but in order to provide you with a workable solution....

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

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

    Re: select rows simulataneously that contain a specific value

    Change all references if and where required.
    Depending on file layout and size, there are better ways like maybe AutoFilter.
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    thank you for the response, I attached a sample sheet, I created a command button to call for the code, but if is possible to click on the command button and then a prompt box will come up and ask for what tosearch and which row to delete. For example, I want to to delete all rows containing from "zzzzperson" the reason why, is that there are over 200000 rows and to search individually its tedious. Thanks in advanced.
    Attached Files Attached Files

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

    Re: select rows simulataneously that contain a specific value

    Re: which row to delete.
    Why?

    Are the names sorted as in your attachment?
    Last edited by jolivanes; 07-22-2019 at 01:54 AM.

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

    Re: select rows simulataneously that contain a specific value

    If your data is sorted, the first code will work also.
    If it is not sorted, the AutoFilter code should do the trick.
    Try on a copy of your original file. There is no "Undo" for code that goes wrong.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    jolivanes thank you!!!! the second code is exactly how I want it.! thank you so much. Thanks to all also.

  8. #8
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    that worked on the sample sheet, however is giving me an error on the actual sheet in this line, the debugger comes up.
    .AutoFilter Field:=1, Criteria1:=who

    not really sure where I need to customize, the column is the same, do I need name the column? it worked immediately on the sample sheet, but can't figure out why it wont work on the actual sheet. Does the number of rows matters? its over 200000 rows.
    Last edited by chubbychub; 07-22-2019 at 07:45 AM.

  9. #9
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    here I attached the actual file, with sample data and the code, there is a command button name 'void' that calls for the macro.
    Attached Files Attached Files
    Last edited by chubbychub; 07-22-2019 at 11:51 AM.

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

    Re: select rows simulataneously that contain a specific value

    When you ask for an invoice number with the InputBox, do you give it a name from Colomn H?

  11. #11
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    yes, I just changed the message, its going to be an invoice number on column H

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

    Re: select rows simulataneously that contain a specific value

    Your original attachment was different from the attachment in Post #9. Post #9 shows that you have a table. You also mention the invoice number in your last Post. I don't see that in your latest attachment.
    I have not worked with tables so you'll need to wait till someone with knowledge on how to AutoFilter tables to come around.
    Sorry about that.
    Good Luck

  13. #13
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    yes,please omit the message,its jut a box asking for a value, either name or invoice or whatever value I enter in thebox on column H. Here is a new attachment
    Attached Files Attached Files
    Last edited by chubbychub; 07-22-2019 at 02:55 PM.

  14. #14
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    Can someone please help? its way out of my knowledge

  15. #15
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    Ok, I found a code that is similar what I want I t to do.

    Please Login or Register  to view this content.
    Can someone help change the look for the entire sheet instead of a range?
    Attached Files Attached Files

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

    Re: select rows simulataneously that contain a specific value

    See if this works.
    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    jolivanes!! thank you !!! you had it in you all this time! it works PERFECT! YOU ARE AMAZING, I was searching all nite for it. Thank you sir!!! will mark as solved

  18. #18
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    jovalines, is there something you can do to clean the code I guess? I tried it works, but one time , it deleted the entire sheet, I enter just wrong data and it deleted the whole sheet of rows, I think if it doesn't find it it will delete all data.
    Last edited by chubbychub; 07-22-2019 at 07:15 PM.

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

    Re: select rows simulataneously that contain a specific value

    I do not know what you did but I used Cesar Rodriguez first followed by Corey Finnley and it removed both as required (17 and 10 Rows)

  20. #20
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    but if you enter lets says "akdjsfksadf" and it don't find it (wrong name or typos), it will erase the entire sheet of rows.

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

    Re: select rows simulataneously that contain a specific value

    You could change this
    Please Login or Register  to view this content.
    to this
    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    844

    Re: select rows simulataneously that contain a specific value

    that works, thank you again.

+ 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. Select specific rows and delete
    By chrissy01 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-18-2019, 04:56 AM
  2. How to find specific work and delete next two rows also select range with specific words
    By priyadharshane in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2019, 03:08 AM
  3. how to select only specific rows corresponding to a specific value in a column
    By valeria3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2018, 07:56 PM
  4. Select or copy rows with specific text
    By rvdkaaij in forum Excel General
    Replies: 0
    Last Post: 07-13-2014, 10:45 AM
  5. Replies: 2
    Last Post: 07-14-2011, 02:27 AM
  6. select specific rows
    By Paulg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2006, 01:15 PM
  7. select and delete specific rows
    By Paulg in forum Excel General
    Replies: 1
    Last Post: 08-22-2006, 11:12 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