How to use Select Case with Not?

[feipezi]

Hi,
If I have about 50 tabs in a workbook but I want to delete 45 of them and only keep 5, I don't want to do this:

...
For each s in Worksheets
Select Case s.Name
Case "tab1","tab2",..."tab40","tab42"
s.Delete
Case Else
End Select
Next
...

As I tried the following, it won't work:
...
Select Case s.Name
Case Not ("tab45","tab46","tab47","tab49","tab50")
s.Delete
Case Else
End Select
...

BTW, the tabs are not attached with a sequential number as I listed, which is just for explanation.
Thanks in advance.

 

[ettienne]

Select Case s.Name
   Case "tab45","tab46","tab47","tab49","tab50"
       'Do nothing
   Case Else
       s.Delete
End Select

 

[AnotherHiggins]

Just flip the logic - do nothing if case matches, delete for Case Else:

Application.DisplayAlerts = False
For Each s In Worksheets
    Select Case s.Name
        Case "tab45", "tab46", "tab47", "tab49", "tab50"
        Case Else
            s.Delete
    End Select
Next s
Application.DisplayAlerts = True

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[DaveInIowa]

This doesn't answer the question asked but may help with the situation you've described.

Instead of relying on the worksheet name (which could possibly be changed by the user), rename the CodeName property (the Name property enclosed in parenthesis) of the 5 worksheets you want to retain from "Sheetx" to "Lockedx". This would remove the hardcoding of the sheet names in the code and allow you to use code similar to the following:

For Each ws In ThisWorkbook.Worksheets
    If (Not ws.CodeName Like "Locked*") Then ws.Delete
Next

 

[feipezi]

Hi folks,
Thanks for the quick return.
But for the Select Case statements, it's not working:

Sub DeleteMost()
Application.DisplayAlerts = False
For Each s In Worksheets
Select Case s.Name
Case "regionlist", "export_payer_data", "Graph_AHY"
Case Else
s.Delete
End Select
Next
End Sub

I had an error of '1004': A Workbook must contain at least one visible worksheet, which means Excel was trying to delete all of them and it did not take the first Case statement.
DaveInIowa, I know your way will surely work but I prefer to use Select Case since if I want to keep 10 out of 50 tabs, I don't want to write about 10 IF/AND statements: 'If ... And...ElseIf... And...'; for Select Case, it will be '...Case "tab1","tab2",...,"tab9","tab10".
I have a macro that can list all the tab names on a spreadsheet. Then I keep the name of the tabs I want (it's easier to delete the name I want IF I want less to keep than delete). So there is no hard-coding involved. I can send you the macro if you want.
Thank you all again and I'll keep trying.

 

[ck1999]

the sheet names will be case sensitive.

ck1999

 

[AnotherHiggins]

DaveInIowa's suggestion has nothing to do with whether you use Select Case or not. He is simply suggesting using the CodeName instead of the name you see on the sheet tab. They are two different things. Notice in the VBEditor, over on the left in the Project Explorer ([Ctrl]+[R] to bring it up if it isn't visible) all of the Sheets are listed. They probably look something like this:

Sheet1 (regionlist)
Sheet2 (export_payer_data)
Sheet3 (Graph_AHY)
....

Well the Codename is the "Sheet1" part. That will stay the same, even if a user renames a sheet from "regionlist" to "rgnlst" - the CodeName can only be changed from within the VBE. So it is a more "bulletproof" approach.

Try this:
Go to Tools > Macro > Security. On the Trusted Publishers tab, check the box beside Trust Access to Visual Basic Project. Click OK. Then run the following macro

Application.ScreenUpdating = False
    ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "Locked1"
    ThisWorkbook.VBProject.VBComponents("Sheet2").Name = "Locked2"
    ThisWorkbook.VBProject.VBComponents("Sheet3").Name = "Locked3"
    ThisWorkbook.VBProject.VBComponents("Sheet4").Name = "Locked4"
    ThisWorkbook.VBProject.VBComponents("Sheet5").Name = "Locked5"

    For Each ws In ThisWorkbook.Worksheets
        If (Not ws.CodeName Like "Locked*") Then ws.Visible = xlVeryHidden
    Next
Application.ScreenUpdating = True

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

"... I have about 50 tabs..." and "...I want to delete 45 of them and only keep 5..."

dim i as integer
for i = 6 to Worksheets.count
  worksheets(i).delete
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I might expect Skip's code to delete every other worksheet (I could be wrong). I would write Skip's code like:

dim i as integer
for i = Worksheets.count to 6 Step -1
  worksheets(i).delete
next

Duane
Hook'D on Access
MS Access MVP

 

[feipezi]

Hi,
A)...worksheets(i).delete... will not work since 1) I don't know which worksheet I want keep or delete; 2) the tabs that I want to keep or delete are not in sequence.

B)I understand DaveInIowa's suggestion has not much to do with Select Case. His point is at CodeName. Again, the CodeName of the sheets is not sequential because some sheets were deleted during processing. For instance, the workbook I am working on now has about 35 tabs but one of the CodeName is "Sheet70".
At least I have visual at both CodeName and regular name of the tabs that I want to keep or delete, i.e., I know which one I want, like the quotation by anotherhiggins above.

C)Yes, the name of the tabs in Sub DeleteMost() has the right case considering being case-sensative but still...

Really appreciate all your efforts. It doesn't look like there is a simple, direct, straightforward way of handling the situation, because 'Select Case s.Name' won't allow:
Case Not ("tab10", "tab25", "tab8")
s.Delete
Case Else...
Take care.

 

[DaveInIowa]

I have a macro that can list all the tab names on a spreadsheet. Then I keep the name of the tabs I want (it's easier to delete the name I want IF I want less to keep than delete). So there is no hard-coding involved.

This is in conflict with your original question. Do you want to identify the worksheets to be saved via a list in another worksheet or do you want to identify the worksheets in a Select/Case statement? If you're insisting on doing it in a Select/Case, you are hardcoding the values.

It doesn't look like there is a simple, direct, straightforward way of handling the situation, because 'Select Case s.Name' won't allow...

There is a way as outlined by both ettienne and anotherhiggins above. Their example code works exactly like how you would like to structure the code. The '1004' error you received is a result of all the worksheets being either hidden or deleted, not a problem with the code sample you were given.

 

[Bong]

Where do you have a list of sheet names that you want to delete? Alternatively, where do you have a list of sheet names you want to keep? Is there some logic other than a lookup table of names to determine whether or not a sheet is a keeper? If so, that logic might be incorporated into a Case or IfThen structure.

You can have the names of the sheets (either to keep or to delete) in a collection. Make sure you add the names as the Key parameter. Then if you try to reference a memeber by name (<collection>.item(key name) with a name that isn't one of the key's you get an error. If you have previously stated: on error goto delRtn, then you can have a series of statements that take the proper action after delRtn:

_________________
Bob Rashkin

 

[SkipVought]

Yes, should have gone from Count to 6 step -1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Hi folks,
DaveInIowa, I agree with you. I tried again with anotherhiggins and ettienne's code on diff. workbooks. Sometimes it works and sometimes it does not. I don't think it has anything to do with tabs being hidden or not; as for tabs having been deleted, it should be true.

Looks like you guys are really serious about my questions and I'd thank you for that. Let me tell you what's going on with my work and I'll try to cut a long story short.
I have a bunch of monthly reports (about 20). Each has a size of 20 to 60 MG before zip, and each has 7 tabs visible (I call them showtabs) but the data tabs behind 7 tabs are hidden.
Because of the size of the workbooks, it's impossible to send them to the clients by email (we use Lotus Notes) since anything emailable must be <= 10 MG and 240 Bytes after zipping. Mine are way over it.
Some clients want only one showtab or part of the workbooks. So I can make it possible to send them by email by deleting all the irrelevant tabs and keep only about 10 tabs which support the single showtab.
Here is what I did.
1) using the macro to list all the tabs in the workbook on a spread sheet;
2) manually delete the tab names that I don't want to keep (or delete the names that I want to keep);
3) cut and paste the names to a SAS (hope you heard of it) pgm and SAS will create a Excel macro like the following in a fraction of a second:

Sub DeleteMost()
Application.DisplayAlerts = False
For Each s In Worksheets
If InStr(s.Name, "Graph_ARB") = 0 And _
InStr(s.Name, "med_ind") = 0 And _
InStr(s.Name, "export_stategraphdata") = 0 And _
InStr(s.Name, "district_name") = 0 And _
InStr(s.Name, "Terr_Payer") = 0 And _
InStr(s.Name, "export_terr_data") = 0 And _
InStr(s.Name, "distpayerlist") = 0 And _
InStr(s.Name, "MonthList") = 0 And _
InStr(s.Name, "Export_State_Natn") = 0 And _
InStr(s.Name, "Export_State_Summ") = 0 And _
InStr(s.Name, "territory_name") = 0 And _
InStr(s.Name, "regionlist") = 0 And _
InStr(s.Name, "export_payer_data") = 0 And _
InStr(s.Name, "Graph_AHY") = 0 Then s.Delete
Next
Application.DisplayAlerts = True
End Sub

Or:

Sub DeleteMost()
Application.DisplayAlerts = False
For Each s In Worksheets
If s.Name <> "Graph_ARB" And _
s.Name <> "med_ind" And _
s.Name <> "export_stategraphdata" And _
s.Name <> "district_name" And _
s.Name <> "Terr_Payer" And _
s.Name <> "export_terr_data" And _
s.Name <> "distpayerlist" And _
s.Name <> "MonthList" And _
s.Name <> "Export_State_Natn" And _
s.Name <> "Export_State_Summ" And _
s.Name <> "territory_name" And _
s.Name <> "regionlist" And _
s.Name <> "export_payer_data" And _
s.Name <> "Graph_AHY" Then s.Delete
Next
Application.DisplayAlerts = True
End Sub

4) I run this macro so the workbook will shrink and be ready for email delivery.
Hope I made myself understood and didn't bore you.
As a matter of fact, I was trying to find out if Select Case can do the same job as IF/AND statements. [blue]Some of you folks may say: why don't you delete the tab names you want keep in the 2) step, and then Sub Deletemost() would be like
...
Select Case s.Name
case "tab2","tab10",...,"tab5","tab25"
s.delete
...
instead of going through the pain 'Case Not....'.
I agree with you 100%. But somehow I'd like to a visual at the tabs that I'm keeping, and I don't want the Sub becomes lengthy.[/blue]
Thanks again.
It's nice to talk with the folks in the forum and this is a great forum.

 

[ck1999]

Just a thought but could you not make a userform and have it load where each sheet has its own checkbox then you can uncheck the ones you want to delete click a button and have the code delete those sheets. This would prevent you from continuosly manually deleting the sheets from your list then using another program for code then inserting the code and running the code.

ck1999

 

[ck1999]

I created a userform with 20 checkboxes numbered 1 - 20 (More can be added but you mentioned 20 sheets)
added 2 commandbuttons cmddelete and cmdend

Private Sub cmdelete_Click()
    counter = 1
    Application.DisplayAlerts = False
    Do While UserForm1.Controls("checkbox" & counter).Visible = True
        If UserForm1.Controls("checkbox" & counter) = False Then
            Sheets(UserForm1.Controls("checkbox" & counter).Caption).Delete
        End If
        counter = counter + 1
    Loop
    Application.DisplayAlerts = True
    UserForm1.Hide
End Sub

Private Sub cmdend_Click()
    UserForm1.Hide
End Sub

Private Sub UserForm_Activate()
    counter = 1
    For Each s In Worksheets
        c = "Checkbox" & counter
        UserForm1.Controls("checkbox" & counter).Caption = s.Name
        counter = counter + 1
    Next s
    For counter2 = 20 To counter Step -1
         UserForm1.Controls("checkbox" & counter2).Visible = False
    Next counter2
    
End Sub

This should do what you want, if I understand your last post.

Open form select which tabs to keep and clock delete!

ck1999

 

[feipezi]

Thanks CK1999.
I'm sure your way of handling the case will work too, and much more formal than the way I am doing now.
I'll keep the code handy in case I need it.
Thanks again.