Excel stops responding but only with this one workbook

[JeffGrant]

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.

 

[igold]

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.

 

[JeffGrant]

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]

You're welcome. I was/am happy to help. Thanks for the feedback.

 

[Alex Blakenburg]

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

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]

@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]

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

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]

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

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]

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.