Excel crashes when running this VBA

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
59
I'm using Microsoft 365 for Business - Excel version 2107

I have a workbook with multiple tabs and have the code below that deletes worksheets that are not listed in the array. I run this code on a monthly basis to generate reports, and the code has worked without any problems in the past.

I'm trying to run the code now and it causes Excel to crash. It will enter the For Loop and delete 2 or 3 sheets and then it crashes. Any ideas, on what is causing the problem?

The day, filepath, and f_name are global variables outside of this sub.

VBA Code:
Option Explicit

Public day As String
Public filepath As String
Public f_name As String


Sub run()

Dim wb As Workbook
Dim myfilename As String
Dim Sht As Worksheet
Dim Arr As Variant
Dim wsName As Variant
Dim Matched As Boolean

    myfilename = filepath & f_name & "Shared Other - " & day & ".xlsx"
    ' open the workbook and pass it to workbook object variable
    Set wb = Workbooks.Open(myfilename)

    Application.ScreenUpdating = False

    ' Delete Tabs that are not listed 
    
    Arr = Array("22-420 Other Special Acti", "22-425 Touring Awards", "22-430 Triple Play Awards", "22-440 Board")
    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

' Save and Close Workbook
wb.Save
wb.Close

End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I take it you run the code from another workbook.
First I would recommend to always be explicit about what you want to do:
VBA Code:
For Each Sht In wb.Worksheets
Reduce unneeded complications JIC:
VBA Code:
Set wb = Workbooks.Open(myfilename, False)
Application.Calculation = xlCalculationManual
Also be aware that Filter returns partial matches as well.
Very Hidden sheets cannot be deleted.
If the number of sheets is not too great you can try to run the code in debug mode and try to find out if the crash appears always at a certain sheet or a random one every time. Or you can temporarily leave the alerts on. Also sometimes the actual operations happening in the background cannot catch up with the code speed - doevents may help in similar cases.
There are really many things that can go wrong so you need to narrow it down.
 
Upvote 0
This code is saved in my personal macro workbook and yes I do execute the code on another workbook.

I appreciate your recommendation and will make those updates.

I'm not exactly sure what was causing the problem but I think I found a temporary solution. I tried running the code on another machine and it worked properly. So it's some type of issue isolated to my machine. I disabled all of the Excel Add-Ins that were loaded and the code works properly after that. It appears that there is some type of conflict with one of the add-ins.
 
Upvote 0
Just a note, you are not switching Application.ScreenUpdating = False back to True. Intentional?
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top