+ Reply to Thread
Results 1 to 18 of 18

Delete unneeded rows based on 2 criteria

  1. #1
    Registered User
    Join Date
    08-19-2015
    Location
    Hartford, CT
    MS-Off Ver
    2013
    Posts
    8

    Delete unneeded rows based on 2 criteria

    Hi I'm looking for VBA help to remove rows of data that are not needed.

    In column M I have a case number with multiple duplicate values. I need to only keep one based on the following criteria (SEE ATTACHMENT):

    - If column K (W_TIME) is greater than 1 then keep row and delete other rows with same case number
    else
    - Find second highest value in column I (T_TIME) and delete other rows with the same case number.


    My VBA skills are rusty and I have 10 files each with >200k lines that need to remove unecessary data before appending them all.

    Thanks in advance!
    Attached Files Attached Files
    Last edited by russman7; 09-09-2020 at 01:55 PM.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Delete unneeded rows based on 2 criteria

    Hi russman7,

    Welcome to the forum!!

    Try this (initially on a copy of your data as the results cannot be undone if they're not as expected):

    Please Login or Register  to view this content.
    I must say this worries me a little...

    I have 10 files each with >200k lines that need to remove unnecessary data before appending them all
    ...because from Excel 2007 a sheet "only" has 1,048,576 rows (less for previous versions) so hopefully there will be many rows deleted, the downside of which is that the code will take longer to process.

    Regards,

    Robert
    Last edited by Trebor76; 09-09-2020 at 08:30 AM.
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

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

    Re: Delete unneeded rows based on 2 criteria

    Another Option...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 09-09-2020 at 02:24 PM. Reason: Added dot
    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!!!

  4. #4
    Registered User
    Join Date
    08-19-2015
    Location
    Hartford, CT
    MS-Off Ver
    2013
    Posts
    8

    Re: Delete unneeded rows based on 2 criteria

    Robert and sintek,

    Thank you both for your assistance. Both of these macros effectively execute my desired output, however to Roberts point above
    the downside of which is that the code will take longer to process
    both take far too long to execute for my current file sizes.

    Thanks again for the help, I'll make this as solved.

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

    Re: Delete unneeded rows based on 2 criteria

    both take far too long to execute for my current file sizes.
    Update a more suitable sample file...Am certain someone can simplify and make the code more efficient...
    Code can be made more efficient with a sort function and delete one block of blanks at once...
    Last edited by sintek; 09-09-2020 at 02:29 PM.

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Delete unneeded rows based on 2 criteria

    See how this non-looping method goes:

    Please Login or Register  to view this content.
    Regards,

    Robert

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

    Re: Delete unneeded rows based on 2 criteria

    Give this a go...Clearing instead of deleting...
    Please Login or Register  to view this content.
    If this does not improve speed then best bet is to store all in array and pass back to sheet in one go...Will need larger sample file though...
    Last edited by sintek; 09-10-2020 at 02:36 AM.

  8. #8
    Registered User
    Join Date
    08-19-2015
    Location
    Hartford, CT
    MS-Off Ver
    2013
    Posts
    8

    Re: Delete unneeded rows based on 2 criteria

    Hi Robert,

    Thank you for trying another method. I executed the code this morning, however it ran for over an hour before I closed the file.

    I'm going to upload another test file with much more data, I suppose that may help testing code on your end.

    Thanks again for your support!

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

    Re: Delete unneeded rows based on 2 criteria

    Just out of curiosity...Have you tried Post 7 code...

  10. #10
    Registered User
    Join Date
    08-19-2015
    Location
    Hartford, CT
    MS-Off Ver
    2013
    Posts
    8

    Re: Delete unneeded rows based on 2 criteria

    Thanks Sintek,

    I used the above code and I think you are onto something with the clearing cells. However, there is one line of code that it's getting stuck on .Range(Str) = 1

    I tried posting a larger file, however my firewall is preventing me from sending the data.

  11. #11
    Registered User
    Join Date
    08-19-2015
    Location
    Hartford, CT
    MS-Off Ver
    2013
    Posts
    8

    Re: Delete unneeded rows based on 2 criteria

    Here is some larger sample data to test with.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    08-19-2015
    Location
    Hartford, CT
    MS-Off Ver
    2013
    Posts
    8

    Re: Delete unneeded rows based on 2 criteria

    I'm afraid I'm not familiar with Post 7 code.

  13. #13
    Registered User
    Join Date
    08-19-2015
    Location
    Hartford, CT
    MS-Off Ver
    2013
    Posts
    8

    Re: Delete unneeded rows based on 2 criteria

    Quote Originally Posted by sintek View Post
    Just out of curiosity...Have you tried Post 7 code...
    Sorry I misunderstood. Yes I did try it, and I think it's promising... please see post #10 comments and post 11's sample data.

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

    Re: Delete unneeded rows based on 2 criteria

    However, there is one line of code that it's getting stuck on .Range(Str) = 1
    That's because this upload is not same as previous...Less 1 column and numbers in Col M are text and not actual numbers...

  15. #15
    Registered User
    Join Date
    08-19-2015
    Location
    Hartford, CT
    MS-Off Ver
    2013
    Posts
    8

    Re: Delete unneeded rows based on 2 criteria

    That's because this upload is not same as previous...Less 1 column and numbers in Col M are text and not actual numbers...
    My apologies. The most recent sample data from post 11 is more closely aligned with the raw data that I'm working with.

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

    Re: Delete unneeded rows based on 2 criteria

    Best I can do...43.7 Seconds
    Untitled.png
    Perhaps someone else can improve...I had to sort your data as you had multiple mismatched numbers all over the place...
    Attached Files Attached Files
    Last edited by sintek; 09-10-2020 at 05:43 PM.

  17. #17
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Delete unneeded rows based on 2 criteria

    Best I can do...43.7 Seconds
    Gee, I feel almost embarrassed to post this as it takes ~ 4 minutes but as I spent some time on it I will anyway.

    Please Login or Register  to view this content.

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

    Re: Delete unneeded rows based on 2 criteria

    Last option...23 seconds...Using my file in post 16
    Please Login or Register  to view this content.
    Last edited by sintek; 09-11-2020 at 02:55 AM.

+ 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] Delete Rows based on Criteria
    By alfaromeo30 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 07-29-2020, 08:47 AM
  2. [SOLVED] Delete unneeded rows of data
    By yukioh in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-24-2012, 02:26 PM
  3. Delete Rows based on Criteria
    By amandaca in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-19-2011, 09:40 AM
  4. Rearrange data (column to row) and delete unneeded rows
    By WaveOfMutilation2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-18-2010, 06:04 PM
  5. Delete Rows based on criteria.
    By excelkeechak in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-28-2010, 12:37 PM
  6. [SOLVED] Delete rows based on certain criteria
    By Coal Miner in forum Excel General
    Replies: 2
    Last Post: 03-03-2006, 02:00 PM

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