Excel crashing when running .Delete

[bbalch]

I'm on Microsoft 365 for business with Excel version 2006 (Build 13001.20266)

Excel is crashing when I run code with .Delete. Any idea how to get around this?

I have some code that deletes sheets not listed in an array. This Example 1 code has worked fine in the past but started crashing excel when I try to run it now. The code below is a simplified version of the Arr list, it would typically be 10 - 15 worksheets. In the example below, assume there are 5 sheets...Sheet 1 - Sheet 5. This code would delete Sheet 2 and Sheet 4.

Example 1

    Dim Arr As Variant
    Dim Sht As Worksheet
    
    Arr = Array("Sheet1", "Sheet 3", "Sheet 5")
    
Application.DisplayAlerts = False
    For Each Sht In Worksheets
        If Not UBound(Filter(Arr, Sht.Name, True, vbTextCompare)) >= 0 Then Sht.Delete
    Next Sht
Application.DisplayAlerts = True

I tried to delete the individual sheets as a workaround and Excel crashed again.

Sheets("Sheet 2").Delete
Sheets("Sheet 4").Delete

 

[steve the fish]

What happens if you delete those sheets manually without code?

 

[bbalch]

If I delete the sheets manually it works as expected. The sheets are deleted.

The message pops up"Microsoft Excel will permanently delete this sheet. Do you want to continue?" Select Delete and the sheet is deleted.