That's not going to work out well. Let's say you have 11 sheets the Sheets() array looks like: Sheets(1).Name = "One" Sheets(2).Name = "Two" Sheets(3).Name = "Three" Sheets(4).Name = "Four" Sheets(5).Name = "Five" Sheets(6).Name = "Six" Sheets(7).Name = "Seven" Sheets(8).Name = "Eight" Sheets(9).Name = "Nine" Sheets(10).Name = "Ten" Sheets(11).Name = "Eleven"
Your INTENT is to delete Sheets Two, Four, Six, Eight, Ten If you use: For I = 2 to Sheets.Count step 2 The first loop, Sheet "Two" is deleted. The Sheets Array then looks like: Sheets(1).Name = "One" Sheets(2).Name = "Three" Sheets(3).Name = "Four" Sheets(4).Name = "Five" Sheets(5).Name = "Six" Sheets(6).Name = "Seven" Sheets(7).Name = "Eight" Sheets(8).Name = "Nine" Sheets(9).Name = "Ten" Sheets(10).Name = "Eleven" In the next Loop, Sheets(4) is deleted, but that is now a sheet named "Five" !!! Also, by the time the loop gets to delete sheets(10), Sheets.Count will only be 7, so an error will occur. Sheets(1).Name = "One" Sheets(2).Name = "Three" Sheets(3).Name = "Four" Sheets(4).Name = "Six" Sheets(5).Name = "Seven" Sheets(6).Name = "Nine" Sheets(7).Name = "Eleven" You should start at the last sheet and work backward: If you KNOW that the number of sheets is 43, and will always be 43, then you can use: For I = 42 to 2 step -2 If you don't know the total number of sheets, you can use: If (Sheets.Count Mod 2 = 0) Then ShtCnt = Sheets.Count Else ShtCnt = Sheets.Count - 1 End If for I = shtcnt to 2 step -2 (the code would be best placed in a "Standard" module, or the Sheet1 module) Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- ________________________________ From: NOORAIN ANSARI <noorain.ans...@gmail.com> To: excel-macros@googlegroups.com Sent: Thu, November 1, 2012 1:35:04 AM Subject: Re: $$Excel-Macros$$ How to delete alternate sheets faster Dear Pravin, Please try Sub delete_alternative_sheet() Dim i As Integer On Error Resume Next For i = 2 To Sheets.Count Step 2 Sheets(i).Delete Next i End Sub On Thu, Nov 1, 2012 at 10:51 AM, Pravin Gunjal <isk1...@gmail.com> wrote: Dear Friends, > > >Greetings ! > > >I am having an excel file (office 2007) containing 43 sheets in it and would >like to delete alternative sheets like 2-4-6 etc. up to the end. > > >Is it possible to select all those sheets at a time and delete the same. As >currently I am selecting one by one and deleting all of them. > > >Thank you, > > >Regards >Pravin Gunjal. > >-- >Join official facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES (1120+ members already BANNED for violation) > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. > >2) Don't post a question in the thread of another member. > >3) Don't post questions regarding breaking or bypassing any security measure. > >4) Acknowledge the responses you receive, good or bad. > >5) Cross-promotion of, or links to, forums competitive to this forum in >signatures are prohibited. > > >6) Jobs posting is not allowed. > >7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. > >NOTE : Don't ever post personal or confidential data in a workbook. Forum >owners >and members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To post to this group, send email to excel-macros@googlegroups.com. >To unsubscribe from this group, send email to >excel-macros+unsubscr...@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros?hl=en. > > > -- With Regards, Noorain Ansari http://noorainansari.com http://excelvbaclinic.com -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
