Excel crashing

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
Folks, I have inherited a report which is causing me some grief. Originally, in .xlsm format it comes in at a hefty 300mb but its only 38000kb in .xlsb. So its not a large file. there are a handful of simple pivots stemming from source data held in dynamic named ranges, and a few navigational macros (ie GoTo next sheet, filter current range, etc). There are some long if statements I plan to look down the track, but nothing too scary.

Just now, I changed tabs and excel still hasn't returned thew screen - its not responding and the screen has gone blank. Is there a method i can employ to find out exactly what it is trying to do when it crashes? that way, I can concentrate of fixing that part of it.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
likely the best thing to do is not fix the file but replace it - maybe with a database

if going the fixing route, try the file without macros enabled. see if they are a problem
review any VBA code & fix any issues
look at the status bar and see if that reveals any problems
check used range on all worksheets
check if there is any conditional formats - and consider optimising/changing
check for any hidden worksheets
check page setups
check named ranges / defined names. remove errors & obsolete ones. replace volatile (dynamic) named ranges (use VBA to create the names instead)
if suitable, remove all formulas. use queries instead
minimise formatting

for sure google will find many more ideas. this is routine stuff

to re-iterate my starting comments, likely it is best to replace the file not fix it. and do that with a smart approach
if you don't have the resources to do that, if warranted, consider engaging an expert/consultant to do it
 
Upvote 0
Thanks Fazza. I hadn't thought of using access. that could very well work. as is commonly the case, when you are in the forest, its hard to see the trees.
 
Upvote 0
When checking the code...makes sure there aren't any Sheet events or calculate events that are firing over and over again.
I'd also consider having a look at each sheet to see where CTRL + End takes the cursor...it might be waay down the sheet / s.
 
Upvote 0
How does one delete properly, permanently, for ever more, never to be seen again, not ever those blank rows below their last row of data? I have just been trying this, as its one of the first things i generally do when taking over someone else's work. In the file I have received, there are blank rows but they refuse to be deleted. its really quite rude of them, don't you think?
 
Last edited:
Upvote 0
What do you mean by permanently deleted...do they still have data in them ?
OR
Do you mean Unseen ?
If the Latter, select the row after the last row of data>>Press CTRL + Down Arrow>>Rclick>> Hide
Same for the columns !

You can't really remove rows forever !!
 
Last edited:
Upvote 0
Hi Michael and Fazza. sorry for not getting back to the thread for a while. I cleaned up the file and changed so of the lookups from entire column ranges to named ranges. In doing so, i needed to then delete the rows below the named ranges. normally, i use ASAP utils to reset the last used cell which deletes columns and rows outside of the used range. However, this wasn't working this time so resorted to manually selecting the empty rows and empty columns and deleting. still didn't work and my last used cell was a long way past the end of my used range. still haven't resolved it but had to move on to something else in the meantime. any suggestions would be greatly appreciated.
 
Upvote 0
Maybe try running this code...save the workbook, then reopen.

Code:
Public Sub UR()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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