re: How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?

Christopher Woods (CustomMade) — Thu, 03 Jan 2013 17:27:07 GMT

@mmoore5553 - an alternative way to think about the problem would be to select the sheet you want to keep (be that the last, the middle or whatever) then figure out the necessary VB code to reorder that worksheet to the first in the document, then run through the delete loop as normal.

Otherwise you'd need to do something like this:

Enumerate all worksheets. Show a list, offering radio buttons, checkboxes or a free text box (akin to the one in any Print dialog) allowing the user to enter the range, comma separated list of page numbers or a single worksheet page number they'd like to keep. Those pages would be saved from the deletion loop by being moved (in order?) to the front of the worksheet list, counted then subtracted from the grand total of worksheets in the documents - with the if loop iterating through until worksheets = the total number of sheets you specified to save.

To delete all worksheets except specified, keeping all worksheets in-place without any reordering would require a more complex loop which was able to 'know' which worksheet IDs it should exempt from the delete procedure. This depends on the functionality Excel offers through VB and whether you can even do something like an enumerate-once-delete-many; I suspect you'd have to work backwards in either case as the IDs likely internally decrement as the leftmost worksheet is deleted.

Another way to attack that would possibly be to enumerate all the worksheets and grep the worksheet NAMES for each of them - then see if VB can delete by worksheet name, because we can see empirically that sheet names persist even if their internal IDs don't.

NB: IANA VB expert; the last time I wrote any VB was about 7 years ago in college so this is just thinking the problem through using coder logic. Perhaps this can be a bumper 2013 follow-on question for Scripting Guy!

re: How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?

mmoore5553 — Fri, 14 Dec 2012 15:04:56 GMT

What would we change to just delete all the rest except the last worksheet ?

re: How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?

Jyotikrushna — Thu, 22 Mar 2012 04:56:34 GMT

Hi,

nice work..thanks a lot to share this code

re: How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?

Paul Damon — Fri, 19 Nov 2010 16:44:23 GMT

Great story; Based on your posting date, I trust that both the physical and psychological scars have healed by now!

Oh yeah... very useful code too!