VBA - Deleting worksheets if its name not in the array-CodePudding

I try to use in_array function which returns True or False depending on value being present in array list and then I want to delete sheets which names are not in the array, but I get Run-time error'9': Subscript out of range after deleting few spreadsheets when there are only two left which names are Nike and second one is random one. Do you know how to fix this ?

Function in_array(my_array, my_value)

in_array = False

    For I = LBound(my_array) To UBound(my_array)
        If my_array(I) = my_value Then
            in_array = True
            Exit For
        End If
    Next
    
End Function

Dim WS_Count As Integer

my_array = Array("Nike", "Adidas")

WS_Count = ActiveWorkbook.Worksheets.Count
 For I = 1 To WS_Count
    test_value = ActiveWorkbook.Worksheets(I).Name
    Z = in_array(my_array, test_value)
        If Z = False Then
            ActiveWorkbook.Worksheets(I).Delete
        End If
 Next I
End Sub

CodePudding user response：

   sub delete_without_asking()
    Application.DisplayAlerts = False 'this stops from asking for each sheet!!!
    Application.ScreenUpdating = False
        my_array = Array("Nike", "Adidas")
        
        For Each blatt In ActiveWorkbook.Sheets
        test_value = blatt.Name
        Z = in_array(my_array, test_value)
            If Z = False Then
                blatt.Delete
            End If
         Next blatt
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   End Sub

CodePudding user response：

there is additional way to check array without looping through array:

my_array = Array("Nike", "Adidas")
For Each sh In ActiveWorkbook.Sheets
    If UBound(Filter(my_array, sh.Name, True, vbTextCompare)) < 0 Then sh.Delete
Next sh