How A Corrupted Workbook Was Repaired
We present a sanitized workbook submitted by a distressed reader of our file corruption article that crashes Excel 2007/2010 after a particular sheet is activated. However, there is no problem, if the file is opened in Excel 2013 or 2016.
My initial thought was that the workbook may have excessive cell formatting since it worked fine in Excel 2013/2016. I guessed that cell format limits may have increased in Excel 2013 compared to 2010, but I soon realized that there was no change in worksheet specifications. Unique cell formats and styles maximum limit is set to 64,000 across all Excel versions.
I started analyzing the corrupted workbook by creating a formula report using the Formula Auditor add-in powered by the Ribbon Commander framework. The Formula Auditor add-in can analyze corrupted workbooks in any Excel version easily, as files are not required to be opened in Excel. Formulas are read from closed workbooks!
The Constr worksheet used range was listed as A1:ER779, but I could not navigate to the last used column, as several columns were hidden, In addition, all rows from 780 onwards were hidden, as well.
My attempt to unhide rows 780:1048576 using the ribbon menu failed. Having recalled myth #10 shown in my Excel Protection Myths Busted article, I used the following statements in the VBE immediate window to unhide and hide these rows 'properly':
ThisWorkbook.Sheets("Constr").Rows("780:1048576").EntireRow.RowHeight = 10
ThisWorkbook.Sheets("Constr").Rows("780:1048576").EntireRow.Hidden = True
After saving the workbook, I opened the file in Excel 2010. To my astonishment, the corrupted worksheet could be activated without crashing Excel. Problem solved!
My initial thought was that the workbook may have excessive cell formatting since it worked fine in Excel 2013/2016. I guessed that cell format limits may have increased in Excel 2013 compared to 2010, but I soon realized that there was no change in worksheet specifications. Unique cell formats and styles maximum limit is set to 64,000 across all Excel versions.
I started analyzing the corrupted workbook by creating a formula report using the Formula Auditor add-in powered by the Ribbon Commander framework. The Formula Auditor add-in can analyze corrupted workbooks in any Excel version easily, as files are not required to be opened in Excel. Formulas are read from closed workbooks!
The Constr worksheet used range was listed as A1:ER779, but I could not navigate to the last used column, as several columns were hidden, In addition, all rows from 780 onwards were hidden, as well.
My attempt to unhide rows 780:1048576 using the ribbon menu failed. Having recalled myth #10 shown in my Excel Protection Myths Busted article, I used the following statements in the VBE immediate window to unhide and hide these rows 'properly':
ThisWorkbook.Sheets("Constr").Rows("780:1048576").EntireRow.RowHeight = 10
ThisWorkbook.Sheets("Constr").Rows("780:1048576").EntireRow.Hidden = True
After saving the workbook, I opened the file in Excel 2010. To my astonishment, the corrupted worksheet could be activated without crashing Excel. Problem solved!
- Have you experienced such a similar issue?
- How did you manage to troubleshoot it?
- Can you demonstrate how this error can be replicated?
Download
Corrupted macro-free workbook for Excel 2007 or later
