Personal.xlsb not closing down with Excel process

mrbenn

New Member
Joined
Jul 9, 2014
Messages
14
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?

Code:
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.
 
Upvote 0
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
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,054
Messages
6,169,835
Members
452,284
Latest member
TKM623

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