Delete rows on a different sheet

  • Okay, I could swear I am doing everything right. I click a button on Worksheet1 (for example) and it calls the following sub which deletes rows on the worksheet CrystalReport.


    Everything runs fine EXCEPT that it is not actually deleting the rows. I am obviously missing something fundamental because I seem to have this issue a lot. I even did a WITH statement like I have been told to do before!


  • Re: Delete rows on a different sheet


    It's hard to say without a workbook to test against, but as a general rule if you are going to loop through rows and delete them you want to loop from the bottom up or you will not get the results you expect.


    When you delete a row while looping from the top down, the row below the deleted row moves up and is skipped by the loop, this is avoided by looping from the bottom up.

    Code
    LastRow to FirstRow Step -1

    Bruce :cool:

  • Re: Delete rows on a different sheet


    Quote from skywriter;773361

    It's hard to say without a workbook to test against, but as a general rule if you are going to loop through rows and delete them you want to loop from the bottom up or you will not get the results you expect.


    When you delete a row while looping top down the row below the deleted row moves up and is skipped by the loop, this avoided by looping from the bottom up.

    Code
    LastRow to FirstRow Step -1


    That was it! Thank you so much!

  • Re: Delete rows on a different sheet


    Quote from apo;773389

    Hi..


    skywriter is right.. top down won't work correctly..


    Another alternative is to build a range to delete using the Union method and delete that range at the end of the loop.. has a bit less sheet interaction.



    I think I will give that a whirl and see if it speeds things up. Thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!