Selecting empty row or column causes Excel to hang, but only in one worksheet

[Canuck 'Eh]

Which is to say it's only one worksheet that causes the problem and not all.

I have a large workbook with 20 inter-dependent worksheets. This workbook is updated daily with an 8mb text file from the ERP system and calculates all manner of things

Within this workbook is a single worksheet that has roughly one million cells - 650,000 in a data table that includes a handful of calculated columns and then a couple of pivot tables that pull from the data table. The data is not formatted as a table.

In an attempt to optimize this spreadsheet, one of the areas I examined was the active range or active cells - using Control-End, and the size is enormous. There's well over 2000 columns with rows right to the bottom - more than 2 billion cells. There's far less than 1% of the active area that has data in it. In trying to reset the size by adjusting the column and/or row width, the macro returns a "Excel cannot complete the task with the available resources" or something to that effect. This is on a multi-core, 64-bit OS with 64-bit Excel and 16gb of ram. If I select a single row or single column, Excel hangs and doesn't come back no matter how long I leave it. While it's hanging, there's no processor or memory overload reported back in the task manager. Perhaps 700,000 k and very low CPU consumption. This is with calculations set to manual, iterations 0, no updating. Selecting a column beyond the end of the range works without issue.

I need to fix this house of cards. The data contained in the pivot tables is accessed by numerous other worksheets so it's a last resort to blow it away and start over, though I'm not sure how I'd manage that even, unless I can recreate the various tables by hand, re-write all the dependent formulas and delete the entire worksheet (without it hanging of course).

Because I can select a full column outside the active cell range, I assume that if I could stop whatever process happens when I select a column within the range, that I could successfully resize the active area and be done with it. Perhaps there's a VBA script to suspend this activity? Certainly open to any and all suggestions.

[mikeTRON]

Do you have any volatile formulas?

I think I would just purge the worksheet and rebuild a new one.

[Canuck 'Eh]

My reticence to purge the sheet all together was tied specifically to the catastrophe that would bring on. It's a veritable house of cards with formulas linking all over the place amongst it's 20 sheets. However - I did manage to solve it.

No volatile formulas, but some ugly ones with vlookups within vlookups and the like. Couldn't reset the active cells with a VBA macro - tried it, it hung the program. Selecting a column hung the program, but selecting a row would eventually recover and you could delete a row, but the entire hang/recover/delete/hang/recover process was 4 or 5 minutes. With roughly 1,000,000 rows to delete, that wasn't an option. Selecting more than one row at a time increased the hang time dramatically. End result - Know what the problem is (if not the cause of the problem), know the solution, can't execute - afraid to delete the sheet entirely, can't delete offending cells.

Solution: While endlessly searching, I came across this little nugget of wisdom - if you change the .xlsm file extension to .zip, it becomes a zip file of XML files. WHAT? Now that's something. Sure enough, that's exactly what happens, and you can browse the zip folder like any other, opening and editing the XML files at will in your favourite editor. In my case, the XML file describing the worksheet contained a cell description for every one of those 2.34 billion cells in a 250 mb XML file. The manner in which the sheetdata tags are formatted allowed an easy elimination of the extra rows, but made columns exceedingly difficult. I shortcut the process, after many attempts at various ideas, by going back into the xlsm file, and duplicating the required data from the offending tab. Straight copy and paste of the pivot tables and formulas so everything landed in exactly the same cell references, just on a new sheet. Save the workbook, delve back into it's XML guts, pull the new worksheet's XML file out, rename it with the offending worksheet's title (which is simply sheet##.xml), drop it back into the zip folder, find and delete the calcChain.xml file (explained below), open the whole works back up in Excel and smooth as butter. A sheet that was chugging along at over 30 seconds on a full calc (87 s. on an M6600 w/16gb & full 64-bit OS & Excel), is now under 5.5. Very happy indeed.

There was no problems from the formulas referencing the offending worksheet as for all they could tell, nothing at all had changed. The name you give your worksheet tabs is connected to a sheet name, sheet## (IE sheet01, sheet12) and an id with the rId tag. I didn't spend a lot of time deciphering what went where, but the actual name on my sheet is not contained with the the XML file that describes the contents of it, so changing that sheet introduced no kinks in the system. The calcChain.XML file is also very easy to deal with. One of my attempts at this introduced the corrupted and/or unreadable data error upon opening in Excel and the log showed a problem in the calcChain file. I toyed with the file in Python but an hour later it was still iterating cell references. It appears that amongst all the other millions of cells, all the extraneous 2.3 billion cells from the offending worksheet are listed, one by one, in the calculation chain, so when it couldn't find them, everyone got upset. However, Excel has this very handy feature - if there's no calcChain.xml, it simply writes a new one from scratch without any complaints or errors.

All told this was a wonderfully challenging, often frustrating, very time-consuming little endeavour, with such an elegantly simple solution at the end of the day.