Yeah, Sam..
I thought about that.
But he said he had 43 sheets!
(OK, he's "only" deleting 21)
That's kind of a cumbersome array to manage,
although I'm sure it could be done.
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: Sam Mathai Chacko <samde...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Cc: Pravin Gunjal <isk1...@gmail.com>
Sent: Thu, November 1, 2012 8:24:16 AM
Subject: Re: $$Excel-Macros$$ How to delete alternate sheets faster
Another way would be to create an array of sheet names and the delete it
accordingly.
So even if you go with a positive Step, it would still work.
Based on the suggestions above, you can come up with the loop... however, in
the
end, the array should be passed like this
ThisWorkbook.Sheets(Array("Sheet1", "Sheet3", "Sheet5")).Delete (example)
Regards
Sam
On Thu, Nov 1, 2012 at 5:46 PM, Ahmed Honest <ahmedhon...@gmail.com> wrote:
Dear Pravin,
>
>Use the solution which suit your need and meets your requirement. But my
>solution will be something like this :
>
>
>By holding Ctrl Key and by using Mouse click select the sheets you wish to
>delete. Once the selection of sheets is done --- right click and choose delete
>--- this will delete all the sheets that you choose = This is also possible if
>you doesnt wish to use the VBA code.
>
>Let me know if you failed to understand.
>
>Thanks
>Ahmed Bawazir
>
>
>
>
>by Clicking by mouse select all the sheets that you wish to delete
>
>On Thu, Nov 1, 2012 at 2:46 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>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.
>>
>>
>>
>
>
>--
>
>Ahmed Bawazir
>احمد باوزير
>
>--
>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.
>
>
>
--
Sam Mathai Chacko
--
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.
