Delete All Sheets between known Sheets?

  • I have to say thanks to everyone here helping us newbies! I have searched the group and learned how to code to delete a sheet, but I wish to delete a range between known sheets. Is this possible?


    I have many sheets , 1-5 do not move when I sort. I sort 6-last.
    6 name = 0000a000
    last sheet name =9999z999


    I want to delete all sheets between them.


    :thanx: Again!

  • the key is whether or not the index positions of the sheet never change.... it is unlikely that this will be the case as the index position is not set by the position of the sheet within the workbook (as is visible) but by the order in which they are added....


    If the index positions were always correct then you could use this:



    In reality though you would be better off stating those sheet names that you do NOT want to delete and cycle the workbook


    eg



    So the first case statement lists those sheets you do not want deleted (no action is taken on that select case, it's simply an identifier), the Case Else will delete any sheet not listed in the previous case statement.
    Doing it this way avoids index problems.
    Turning the displayalerts off means you will not be prompted every time you delete a sheet (Do You Want to Delete This Sheet?), you must ensure you reinstate it once done however


    Hope that helps.

  • Sweet! Thanks, Good approach I did not think of.


    Thank you very much. I have just found this forum, and I love it. I just started writing VB, and have learned a great deal. There are many talented people here including yourself. Thanks again, and have a great day.


    :thanx:

Participate now!

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