I have an enormous workbook with a large number of interdependent worksheets, a beast that has grown more ungainly over time. The pre-optimization full calc speed on a dual-core i7 with 16gb was 85 seconds on 64-bit Excel (average of 10 attempts). The only way to use it is to have calculation on manual and just update as required. It is an unpleasant tool to use, hence the pursuit of optimization.
In running individual sheet timer calcs, one worksheet stuck out like a sore thumb. Using control-end to find the last active cell puts me in column CGU (2281) and row 1048572 which translates to more than 2.3 billion cells in the active range. I have less than 500,000 actually in use. Ideally, I should be able to highlight the empty areas and delete them, or use one of the available tools to reset the active range, but nothing has worked. Highlighting a single row (less cells than highlighting a single column) grinds the machine to a halt but will eventually allow you to delete it. But I have roughly 1 million rows to delete, so that will take a very long time, and I'd still be left with the thousands of extraneous columns. This grinding behaviour happens even with calculations set to manual.
I'm resigned to the fact that I will have to replace the worksheet entirely, but the number of interdependent formulas spread through the balance of the workbook make this a horrifying prospect as it would take days to find all of the broken links.
I am looking for suggestions on how I might minimize the pain. Also looking for insight as to why Excel freezes when in manual mode just highlighting an empty row or column. On any other sheet, the same action does not elicit the same response.
In running individual sheet timer calcs, one worksheet stuck out like a sore thumb. Using control-end to find the last active cell puts me in column CGU (2281) and row 1048572 which translates to more than 2.3 billion cells in the active range. I have less than 500,000 actually in use. Ideally, I should be able to highlight the empty areas and delete them, or use one of the available tools to reset the active range, but nothing has worked. Highlighting a single row (less cells than highlighting a single column) grinds the machine to a halt but will eventually allow you to delete it. But I have roughly 1 million rows to delete, so that will take a very long time, and I'd still be left with the thousands of extraneous columns. This grinding behaviour happens even with calculations set to manual.
I'm resigned to the fact that I will have to replace the worksheet entirely, but the number of interdependent formulas spread through the balance of the workbook make this a horrifying prospect as it would take days to find all of the broken links.
I am looking for suggestions on how I might minimize the pain. Also looking for insight as to why Excel freezes when in manual mode just highlighting an empty row or column. On any other sheet, the same action does not elicit the same response.