+ Reply to Thread
Results 1 to 35 of 35

Delete rows - faster way?

  1. #1
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Delete rows - faster way?

    Hi all

    I have 2 questions that I am hoping you can assist me with. First, I am using the below macro to delete rows. The first problem is that I can delete the value that I nominate, e.g. "AUS" below, but how to amend the code below to delete EVERY OTHER VALUE?

    Second, the code below works but I have over 200,000 rows in my sheet. Is there a way to amend it so it runs faster? It is extremely slow right now

    Thanks

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    extrapolate
    "how to amend the code below to delete EVERY OTHER VALUE"
    Do you mean everything but AUS?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    Yes, that is correct. Sorry I should have been clearer. I have been able to get around that by using:

    Please Login or Register  to view this content.
    which will delete all values EXCEPT Aus and NZL.

    However it is extremely slow to run. Do you know of a more efficient process?

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    hmm the autofilter method is generally quick already

    have you tried turning these additional things to turn off (and back on)
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False

  5. #5
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    I just tried it again with about 50,000 rows. It still took about 30 secs.

    Also, I can now only get "AUS" values. It seems the code above is deleting the "NZL" when it is supposed to remain?

  6. #6
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Delete rows - faster way?

    can you sort it? If so is this fast?

    Please Login or Register  to view this content.
    Probably much faster doing a count rather than a loop but we can look at that sort of thing if my idea is of any use at all.

    Edit ok did it anyway

    Please Login or Register  to view this content.
    Last edited by scottiex; 05-07-2017 at 11:41 PM.
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete rows - faster way?

    Have you tried advanced filter and output to other range?

    Sample.
    Assuming col.J is blank outputting P1
    Please Login or Register  to view this content.
    Last edited by jindon; 05-07-2017 at 11:34 PM.

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    Please Login or Register  to view this content.
    you had an extra = sign

    Please Login or Register  to view this content.
    Scottiex sort was going to be my next proposal =p
    but i only difference is i would put in a helper column to resort back to original order

    Jindon's solution bypasses the deleting all together which would be the "slow" part of your code

  9. #9
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    thanks humdingaling.

    Scottiex - I tried the sort method on 50,000 rows and it seemed much faster. I am going to try on more rows and see how it goes.

    One question - how can I amend your code to also include values = "NZL"? I want to keep these as well.

    Thanks

  10. #10
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Delete rows - faster way?

    Maybe this.. extra lines added also in the code to allow for unlikely chance that there are no NZL or AUS entries

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    thanks scottiex but unfortunately no "NZL" values appear in my data after I run the macro?

  12. #12
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    it also appears to not pick up all of the "AUS" values?

  13. #13
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Delete rows - faster way?

    ok lets go back to the simple way MAYBE... I assume you always have AUS and NZL values here.

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    yes, there will always be values for AUS and NZL. That seems to have done the trick, thank you

  15. #15
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    Also, I have another column where I need to perform a similar delete, but the text in each row to find is within a string. E.g. in col D some will read "trees and apples" and I need to delete all occurrences rows with "apples".

    Could the above code be adapted to achieve this?

  16. #16
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    *not as quick as scottie it would seem*
    Alternate method since i did it already

    Please Login or Register  to view this content.
    i would probably prefer the advance filter method if the structure doesnt matter in the end
    Last edited by humdingaling; 05-08-2017 at 12:58 AM. Reason: xlAutomatic

  17. #17
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    stealing jindon's code

    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    thanks humdingaling, that also works well.

    Could this code be adapted to solve for my other question?

  19. #19
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    Which Column...all?

  20. #20
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    sorry, no only on Col D

  21. #21
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    Please Login or Register  to view this content.
    ps i would leave the Column delete out in testing phase

  22. #22
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    thanks humdingaling, that works great

    Say I wanted to pick up all the values in another column and search for these in col D and delete them? Could this be achieved with the above code? E.g. I have values in col K (it also contains blank cells) and I would like to find these in Col D and delete the entire row if found?

    Thanks

  23. #23
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    well firstly...if you have data in column K you need to move out where the criteria for the advanced filter is placed

    pick up all the values in another column and search for these in col D and delete them
    plausible taking the principles of advanced filter...it wouldnt be this simple a code

  24. #24
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    yes I thought about that but also the values are different so I could not use [K2] = "<>*Apples*".

    Is there another way to do this?

  25. #25
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    tried it..doesnt work on my tiny sample. ill imagine it will not work in your real life file

    this latest question is really different from your original problem

    i dont imagine any code that requires what you are asking to do be fast
    it would really depends on how many values you are talking about but for each additional value would be greatly increase time taken to perform the task

  26. #26
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    ok thanks humdingaling. Perhaps I should post this question in a new thread?

  27. #27
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    does your original code do what you want only slower? or are you actually asking something completely different and as such speed is not an issue?

    if the later then probably yes

  28. #28
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    Quote Originally Posted by humdingaling View Post
    *not as quick as scottie it would seem*
    Alternate method since i did it already

    Please Login or Register  to view this content.
    i would probably prefer the advance filter method if the structure doesnt matter in the end
    Sorry to bring up a previous thread but this is now deleting ALL my data, rather than just the rows with value of "N" ( I have highlighted the row where it deletes all my data). Does anyone know why this is happening?

  29. #29
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    did you update what you are trying to look for but not update the formula?
    debug your code and pause before the delete

    is the helper column doing what its meant to be doing?

  30. #30
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    nothing has changed. For some reason it is working on my sample sheet but not my real one? I'm really confused on this one!

  31. #31
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    Here is another sample where it is not working. The only thing I have added is the first line to delete the irrelevant columns. The macro deletes all data though.
    Attached Files Attached Files

  32. #32
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    have you went thru and debugged line by line to make sure the outcomes are expected all the way up the deletion

    ps in debugging...leave application showing as normal just in case it messes something up

    Please Login or Register  to view this content.

  33. #33
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    yes I went through line by line and everything is as expected until that line I highlighted earlier when it deletes all data?

  34. #34
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Delete rows - faster way?

    its this line
    change A2 to A1

    Please Login or Register  to view this content.

  35. #35
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: Delete rows - faster way?

    yes that has done it! Thank you humdingaling!

+ 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] Faster way to delete rows?
    By maym in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-08-2016, 02:32 AM
  2. Is there a faster way than a loop to delete rows that satisfy a criteria
    By mortphil in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-28-2014, 12:14 PM
  3. [SOLVED] More efficient and faster way to delete Rows?
    By nironto in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-07-2013, 05:01 AM
  4. Any faster way to delete hidden rows of data
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-20-2012, 02:58 PM
  5. Replies: 2
    Last Post: 01-16-2010, 09:27 AM
  6. how to delete faster in macro
    By kaffal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2009, 11:26 AM
  7. Faster way to delete?
    By jenrenea1 in forum Excel General
    Replies: 1
    Last Post: 05-06-2005, 05:17 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