CODEWITHSUNDEEP
vbaexcel
Max888
Max888

Reputation: 3770

How to delete all sheets except those in list

I have some VBA to delete all the sheets in a workbook.

For Each ws In ActiveWorkbook.Worksheets
    ws.Delete
Next ws

how can I amend this to exclude some cases in a list? pseudo code would look like:

exclude = Array("apple", "orange")
For Each ws In ActiveWorkbook.Worksheets
    if ws not in array then ws.Delete
Next ws

Upvotes: 0

Views: 2401

Answers (1)

Tom
Tom

Reputation: 9878

Use this function to test if it is in your array

Public Function InArray(arr As Variant, SearchValue As String) As Boolean
    InArray = (UBound(Filter(arr, SearchValue)) > -1)
End Function

For Each ws In ActiveWorkbook.Worksheets
    If Not InArray(exclude, ws.Name) Then ws.Delete
Next ws

Update for all on one line

For Each ws In ActiveWorkbook.Worksheets
    If UBound(Filter(arr, ws.Name)) = -1 Then ws.Delete
Next ws

or use @TimWilliams suggestion in the comments above

For Each ws In ActiveWorkbook.Worksheets
    If Not IsError(Application.Match(ws.Name, exclude, 0)) Then ws.Delete
Next ws

Upvotes: 3

Related Questions