Excel stops responding but only with this one workbook

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have a workbook which is 34MB, has 15 Sheets but is heavily laden with formulas and a little bit of formatting.

For quite some time I have been experiencing issues where Excel Stops responding, i get the not responding message at the top of the screen, the fan kicks in after about 20 seconds and then Excel will continue.

If I only open the file, and do an immediate close and don't save, that process alone can take up to 40s.

I have deleted all add-ins, removed all of my anti virus protection, removed everything from auto startup, done three Office 365 online repairs.

I am running an Acer Aspire 5 notebook, with 20Gb Ram external monitor.

On a regular basis, I will get a macro cant run error for a custom group, and then when I delete the macro from the group and re-add everything is fine.

But tonight I have this new problem. The macros don't even appear for me to re-add them.

It only happens with this workbook, so clearly it is related to this workbook.

As a precaution, I do a manual backup multiple times per day.

I am at a loss and getting really frustrated.

Has anybody got some ideas why this workbook is giving me so much grief?

Really hoping that the good folk on this forum have some ideas.

J.


UPDATE: I just did a File Open Open & Repair, I the Macros now appear in the box below.

All suggestions are graciously received.

1629548878872.png
 
Last edited:

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,705
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I think if you keep going in the direction you are, you will have your issues under control. There may be some grunt work involved but at least you'll have your book back.
 
Solution

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
You have helped me immensly. Now that I know to look for terms like phantom ranges, then I can research how they occur and what I need to do resolve them.

I can not thank you enough.........
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,705
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You're welcome. I was/am happy to help. Thanks for the feedback.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows
You have helped me immensly. Now that I know to look for terms like phantom ranges, then I can research how they occur and what I need to do resolve them.

I can not thank you enough.........

I think you are pretty well sorted now but just to round off the discussion. I see that you use VBA.
Below is a copy of @Michael M's macro to reset the end of sheet for all sheets ( I had my own but his is more streamlined).
PS: There was a reference to Saving and Re-opening the spreadsheet for Ctrl+End to be reset after the cleanup but certainly in current versions of Excel just hitting Save is enough. You can also run in VBA or the immediate window the command Activesheet.UsedRange

Conditional formatting is volatile, so having it on thousands of rows is not doing your performance any favours.
Inserting rows and pasting in additional data, can fragment the conditional formatting rules (creating multiple rules for different ranges)
Debra Dalgleish has a video and clean up macro (for Excel Tables) here:-
Excel Conditional Formatting Introduction

VBA Code:
Sub MM2_ResetAllSheets()
    Dim x As Long, LastRow As Long, LastCol As Long
    Application.ScreenUpdating = False
    On Error Resume Next
    For x = 1 To Sheets.Count
        With Sheets(x)
            LastRow = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
            LastCol = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With
    Next x
    On Error GoTo 0
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Sub
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,705
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

@Alex Blakenburg - absolutely not the case, with regards to this statement.

PS: There was a reference to Saving and Re-opening the spreadsheet for Ctrl+End to be reset after the cleanup but certainly in current versions of Excel just hitting Save is enough.

I am running the latest version of Excel on a very capable machine and saving a deleted range does not always keep it deleted. I guess it's one of those things you have to experience for yourself.
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
I think you are pretty well sorted now but just to round off the discussion. I see that you use VBA.
Below is a copy of @Michael M's macro to reset the end of sheet for all sheets ( I had my own but his is more streamlined).
PS: There was a reference to Saving and Re-opening the spreadsheet for Ctrl+End to be reset after the cleanup but certainly in current versions of Excel just hitting Save is enough. You can also run in VBA or the immediate window the command Activesheet.UsedRange

Conditional formatting is volatile, so having it on thousands of rows is not doing your performance any favours.
Inserting rows and pasting in additional data, can fragment the conditional formatting rules (creating multiple rules for different ranges)
Debra Dalgleish has a video and clean up macro (for Excel Tables) here:-
Excel Conditional Formatting Introduction

VBA Code:
Sub MM2_ResetAllSheets()
    Dim x As Long, LastRow As Long, LastCol As Long
    Application.ScreenUpdating = False
    On Error Resume Next
    For x = 1 To Sheets.Count
        With Sheets(x)
            LastRow = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
            LastCol = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With
    Next x
    On Error GoTo 0
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Sub
Thank you Alex...

I have to say that the assistance you folk give is out of this world. This problem alone has caused me to literally throw the laptop out of the window in shear frustration. This problem has been going on for months and just didn't know what to look for.

I will be sure to implement this code and review the video this afternoon.

And in a matter of hours with the priceless assistance from igold, Excel Max and now yourself, this Acer Laptop has been saved from certain crushing at the local recycling plant.
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
I think you are pretty well sorted now but just to round off the discussion. I see that you use VBA.
Below is a copy of @Michael M's macro to reset the end of sheet for all sheets ( I had my own but his is more streamlined).
PS: There was a reference to Saving and Re-opening the spreadsheet for Ctrl+End to be reset after the cleanup but certainly in current versions of Excel just hitting Save is enough. You can also run in VBA or the immediate window the command Activesheet.UsedRange

Conditional formatting is volatile, so having it on thousands of rows is not doing your performance any favours.
Inserting rows and pasting in additional data, can fragment the conditional formatting rules (creating multiple rules for different ranges)
Debra Dalgleish has a video and clean up macro (for Excel Tables) here:-
Excel Conditional Formatting Introduction

VBA Code:
Sub MM2_ResetAllSheets()
    Dim x As Long, LastRow As Long, LastCol As Long
    Application.ScreenUpdating = False
    On Error Resume Next
    For x = 1 To Sheets.Count
        With Sheets(x)
            LastRow = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
            LastCol = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With
    Next x
    On Error GoTo 0
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Sub
HI Alex, this code now begs the question..How often you this be run.

Because my model involves two specific sheets where the number of rows can vary by the thousands on any day, perhaps this code should be the last macro to run everyday as part of the automatic process.

Would you have thoughts about that?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows
It depends a little on how you are clearing the data.
Deleting rows and columns should reset the used range and "clear all" should as well.
Clear Content does not.

Either review the spreadsheet occasionally when it starts to blow out and run it then or if it is blowing out regularly and assuming the code doesn't take long to run then sure incorporate it into the process.
 

Forum statistics

Threads
1,147,734
Messages
5,742,862
Members
423,760
Latest member
photogfrog

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
Top