*Hi Paul,* * * *Could you please inform me the full code, so that I can apply it in the attached file for test purpose and later on in my original file for use.* * * *And thanks to all for their solutions too.* * * *Regards* *Pravin Gunjal*
---------- Forwarded message ----------
From: Paul Schreiner <schreiner_p...@att.net>
Date: Thu, Nov 1, 2012 at 5:16 PM
Subject: Re: $$Excel-Macros$$ How to delete alternate sheets faster
To: excel-macros@googlegroups.com
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://
<http://www.noorainansari.com/>noorainansari.com<http://www.noorainansari.com/>
http://
<http://www.excelvbaclinic.blogspot.com/>excelvbaclinic.com<http://www.excelvbaclinic.blogspot.com/><http://accesssqclinic.blogspot.in/>
--
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.
--
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.
Book1.xlsb
Description: Binary data
