Excel crashing

[ajm]

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.

 

[Fazza]

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

 

[ajm]

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.

 

[Michael M]

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.

 

[ajm]

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?

 

[Michael M]

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 !!

 

[ajm]

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.

 

[Michael M]

Maybe try running this code...save the workbook, then reopen.

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