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?
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!
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
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
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.
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