Excluding Collection Members

I saw a post in the newsgroups this week about looping through the sheets of a workbook to delete them. There were several sheets that the poster didn’t want to delete. The answer provided was to use a Select Case statement with all the excluded sheets’ names in a Case statement.

For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case “Save1?, “Save2?, “Save3?
        Case Else
            ws.Delete
    End Select
Next ws

I’ve run into this problem before, but never found what I would consider an elegant solution. I don’t like the Select Case solution because I will invariably have to perform some other action later in the code that excludes those same members. That means that I’ll have to retype all the sheet names in a Case statement. I prefer to have all the excluded members in one place near the top of the procedure. It makes for easy editing when the list needs to be changed.

Typically, the way that I handle this is with a String and the Instr function. Like in this procedure:

Sub exclusion()

    Dim SheetsToKeep As String
    Dim i As Long
    Dim ws As Worksheet
    
    ‘Names of sheets to keep
    ‘note the last comma
    SheetsToKeep = “Save1,Save2,Save3,”
    
    ‘Loop through the sheets
    For Each ws In ThisWorkbook.Worksheets
        ‘See if the sheet’s name is in the string
        ‘don’t forget the comma
        If InStr(1, SheetsToKeep, ws.Name & “,”) = 0 Then
            ws.Delete
        End If
    Next ws
    
End Sub

I don’t particularly like this method, but it’s the best I’ve been able to do. I like the fact that all the excluded sheets are in one place at the top. If I need to adjust the list, I only change it in one place. How do you exclude certain members of a collection when you loop through them?

Posted in Uncategorized

5 thoughts on “Excluding Collection Members

  1. I would take your second approach. If you didn’t want to repeat the exclusion check code every time, make a function to return true/false.
    eg.
    For Each ws In ThisWorkbook.Worksheets
    If Not ExclCheck(ws.Name) Then ws.Delete
    Next ws

    I try to use a separator character which is likely not be used for sheet names.
    Instead of comma, You could use section-break ?
    It’s easy to type it ALT, numkey2, numkey1

    Then again, using an exclusion check function you would probably use an array rather than CSV

    Cheers!

  2. Problem: What if one of the sheet names is a suffix of another sheet name?

    For instance, if you have MySheet1 and Sheet1 and you want to delete Sheet1.

    The code supplied would delete MySheet1 too.

    I’d use this code instead:

    Sub exclusion()
    Dim SheetsToKeep As String
    Dim i As Long
    Dim ws As Worksheet

    ‘Names of sheets to keep
    ‘note the first and last commas
    SheetsToKeep = “,Save1,Save2,Save3,”

    ‘Loop through the sheets
    For Each ws In ThisWorkbook.Worksheets
    ‘See if the sheet’s name is in the string
    ‘don’t forget the commas
    If InStr(1, SheetsToKeep, “,” & ws.Name & “,”) = 0 Then
    ws.Delete
    End If
    Next ws

    End Sub

  3. It would be best to create a new collection of sheets to keep.
    This would eliminate the separation character and the test for the sheet.
    The only problem is the atrocious way VBA handles errors.

    Sub exclusion()
    Dim Keep As String
    Dim ws As Worksheet
    Dim SheetsToKeep As Collection

    ‘ Create a new collection
    Set SheetsToKeep = New Collection

    ‘Add Names of sheets to keep
    ‘The “” can be anything,
    ‘it’s the key we are interested in
    SheetsToKeep.Add “”, “Save1?
    SheetsToKeep.Add “”, “Save2?
    SheetsToKeep.Add “”, “Save3?

    ‘Loop through the sheets
    On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
    Err.Clear
    Keep = SheetsToKeep(ws.Name)
    ‘if the sheet is part of the collection
    ‘err is 0, otherwise err is not 0
    If Err.Number <> 0 Then
    ws.Delete
    End If
    Next ws
    On Error GoTo 0
    End Sub

  4. I like JT’s example.

    One additional piece of advice — add these lines to avoid having to approve the deletion of every sheet:

    Application.DisplayAlerts = False
    Application.DisplayAlerts = True

    If you wrap them around ws.Delete then it won’t prevent any other alerts from displaying.

  5. I’d suggest setting up a dictionary object, like this:

    Dim d
    Sub CreateSheetDictionary()
    Set d = CreateObject(“Scripting.Dictionary”)
    d.Add “sheet1?, “1?
    d.Add “sheet4?, “1?
    d.Add “sheet5?, “1?
    End Sub

    Then, your delete routine looks like this:

    For Each ws In ThisWorkbook.Worksheets
    if not(d.exists(ws.name)) then
    ws.delete
    end if
    Next


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *