Personal.xlsb not closing down with Excel process

[mrbenn]

I have a few macros saved into my Personal.xlsb file which work as expected. The problem is that when i close Excel and look in Task Manager there is still at least one Excel.exe running which gradually builds up in memory until it causes the whole system to slow down. Having googled around I tried removing the Personal.xlsb files from the XLStart folder and without this Excel opens, works and closes as expected.

The macro i suspect is causing the problem is this one, it is the only one I have run today or since the PC rebooted last but I am at a loss to figure out what is happening?

Sub avail231()'
' 231Availability Report
'
'
'
Dim sSheetName As String
    Dim sDataRange As String
    sSheetName = ActiveSheet.Name
    sDataRange = Selection.Address
    strActiveWorkSheet = ActiveSheet.Name
Cells.Select
Columns.AutoFit
Cells(1, 1).Select
Dim rangeTemp As Range
Set rngTemp = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not rngTemp Is Nothing Then
    Range(Cells(1, 1), rngTemp).Select
End If
Range("A1").Select
'1ST level SORT ON NAME COL A
'2ND level SORT ON START DATE COL B
'3RD levle SORT ON END DATE COL C
Sheets(sSheetName).Range("a1:u" & Sheets(sSheetName).Range("a1").End(xlDown).Row).Sort _
key1:=Sheets(sSheetName).Range("D:D"), order1:=xlAscending, _
key2:=Sheets(sSheetName).Range("B:B"), order2:=xlAscending, _
Header:=xlYes
'    Range("A1").Select
'    Selection.AutoFilter
'    ActiveSheet.Range("A:U").AutoFilter Field:=4, Criteria1:="BR50"
'    ActiveSheet.Range("A:U").AutoFilter Field:=6, Criteria1:="0"
'    ActiveSheet.Range("A:U").AutoFilter Field:=8, Criteria1:="1"
'    Selection.SpecialCells(xlCellTypeVisible).Select
'   Application.DisplayAlerts = False
'    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
'    Application.DisplayAlerts = True
'ActiveSheet.ShowAllData
Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("A:U").AutoFilter Field:=4, Criteria1:="KL20"
    ActiveSheet.Range("A:U").AutoFilter Field:=2, Criteria1:=Array("EX/CLAMP", "EX/GSKT", "EX/MTG", "EX/TUBIN"), Operator:=xlFilterValues
    Selection.SpecialCells(xlCellTypeVisible).Select
   Application.DisplayAlerts = False
   ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    ActiveSheet.ShowAllData
Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("A:U").AutoFilter Field:=1, Criteria1:=Array("CLUTCH/CAR", "EXHAUSTS", "ROTATE", "T/MISSION"), Operator:=xlFilterValues
    ActiveSheet.Range("A:U").AutoFilter Field:=6, Criteria1:="0"
    ActiveSheet.Range("A:U").AutoFilter Field:=8, Criteria1:="1"
    Selection.SpecialCells(xlCellTypeVisible).Select
   Application.DisplayAlerts = False
   ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    ActiveSheet.ShowAllData
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
Range("A1").Select
End Sub

Any help anyone could give would be very gratefully received

 

[AFPathfinder]

Is the Excel application closing or waiting for your PERSONAL.xlsb to finish? To me it sounds like you have a Workbook_BeforeClose event that may have a loop that is storing values. Check your "ThisWorkbook" object for code.

 

[mrbenn]

the application "closes" (it is no longer showing any open windows on screen) but if I open Task manager it is still showing EXCEL.EXE as running and increasing in memory usage (over 700mb usually!)

Only thing I see in Thisworkbook "Beforeclose" is

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

 

[AFPathfinder]

Are you using any COM addins?

 

[mrbenn]

I have ASAP utilities installed and it puts in a COM addon to run it but I have the same issue with that disabled or even ASAP uninstalled completely. That is the only one that is enabled, Power Map for Excel is shown in the list but unticked

 

[AFPathfinder]

As far as the macro you provided in your initial post, there are a couple things you could change to have less of a memory footprint, but I don't see anything in there that would be causing the issue. How often do you run that macro while Excel is open?

 

[mrbenn]

once per day early in the morning. I am really guessing that this is the problem as I have tried everything else I can think of

 

[GTO]

What year/version Excel are you using? I only have 2010 and 2003 available, so may not be able to help, but I did notice a thread where the OP states issues with Excel startup in 2013/2016 (specifically an issue with having an XLSM file in XLSTART). Anyways, just thought to see if this is possibly version related...

Mark

 

[mrbenn]

Sorry, I should have specified that, Excel 2013 is the version I am using and the XLSTART folder contains only that personal.xlsb file (in C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART)

 

[GTO]

Okay, probably of no help, but try from a fresh boot:

• After the fresh boot: Start Excel from the Start button (i.e. without opening any workbooks
• After the fresh boot: Start Excel via Windows Explorer by double-clicking an Excel workbook (so that we are starting Excel with an open workbook)

In both tests, look in Task Manager (Processes tab) and see if only one instance of Excel is listed, or are there two?