Excel capacities - how to deal with a functionally uneditable file (block of large array formulas almost undeletable)

[98apple]

Apologies if I’m in the wrong forum please redirect me. Large (~300mb) .xlsm project ridiculously slow for months (½ hour just to load). Finally tracked it down I think to somehow a complex array formula referencing several sheets mistakenly copied into 200,000+ cells. Now even trying to delete or clear those cells sends Excel off into Not Responding land never to return. Clearing the cells one at a time works but Excel takes over a minute to return after delete key pressed to clear just one cell. (Yes of course automatic calculation is off). So desperate I wrote a tiny macro to clear them one at a time, ran all last night, deleted only ~200... at this rate will take 1000+ days. Apparently problem has existed for awhile so it’s present in all backups anywhere near useful. Offending sheet has too many links to other sheets in the file to try to separate/copy/reproduce it. Flailing about trying stuff: safe mode, etc. 64-bit Excel 2013, W10 Pro, 64g i7-7700. Help me Obi-Wan!

 

[tonyyy]

98apple,

Welcome to the Board.

So desperate I wrote a tiny macro to clear them one at a time...

Can you share your code please? And how many rows/columns are in your data set? Can you post some sample data?

Thanks,

tonyyy

 

[98apple]

tonyyy, thanks! Tried to attach screen shot but says I may not post attachments or maybe I’m just new here and missed the link. The big workbook itself is both ungainly (300mb+) and client doesn’t allow me to share it, but screen shot is explanatory. Structure is about 15 sheet tabs containing data and relatively simple equation transforms of it, referenced/gathered onto a summary sheet tab. It is on that summary tab (‘Action’) that somehow someone (that would almost certainly be me) unknowingly copied a complicated array formula to DWS649-DWX223479. An example of the offending array formula (and 200,000+ rows of its bretheren) from DWS649 is:



=AVERAGE(IF(('RK(MAp)'!$D649:$DWK649>(1-Cutoff+Center))*(Price!$D649:$DWK649>Long),'%'!$D650:$DWK650))-AVERAGE(IF(('RK(MAp)'!$D649:$DWK649<(Cutoff2+Center))*(Price!$D649:$DWK649>Short),'%'!$D650:$DWK650))-2*Trans



Doesn’t make sense why Excel takes so long just to do a simple delete/clear a cell. The number of dependent cells for these culprits is ZERO.



Little code fast ‘n dirty I wrote to try to automate deleting ‘em (took all night for 200 rows):

Sub DeleteThis() '^X

If MsgBox("DeleteThis?", vbOKCancel) = vbCancel Then End

Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual

outRow = Range("StartDelete").Row: outCol = Range("StartDelete").Column

lastTime = Now()

MoreMoreFish:

Application.StatusBar = outRow & "/223479" ‘show me how far along we are

outRow = outRow + 1 ‘next row

If IsEmpty(Cells(outRow, outCol)) Then GoTo DoneDeal ‘are we done yet?

Cells(outRow, outCol + 0).ClearContents ‘clear 6 cells

Cells(outRow, outCol + 1).ClearContents

Cells(outRow, outCol + 2).ClearContents

Cells(outRow, outCol + 3).ClearContents

Cells(outRow, outCol + 4).ClearContents

Cells(outRow, outCol + 5).ClearContents

If Abs(Hour(Now()) - Hour(lastTime)) < 3 Then GoTo SkipSave 'save roughly every 6 hours

ThisWorkbook.Save: lastTime = Now() 'save, reset counter

SkipSave:

GoTo MoreMoreFish

DoneDeal: MsgBox ("Done")

End Sub



Again, there is no functional or logic problem with the formula, sheet or workbook. It does what it’s supposed to do (or, it would, if it weren’t so ungainly that it didn’t stress Excel to the breaking point). The tools, context, design, etc. are legacy so I have limited ability to redesign the thing.

 

[Eric W]

Have you tried deleting them all at once? You certainly could write a little macro to do this, but it's easy enough to do from Excel. Select the range with the offending formulas. If you know the boundaries explicitly, just enter the range in the name bar (left of the formula bar). If the range is L10:Q20, put L10:Q20 there, and the whole range will be selected. Now just press the delete button. If you don't know the explicit range, select the top left cell, and use Control+Shift+downarrow, and Shift+rightarrow 5 times to make the selection.

If there are no other formulas on the sheet you want to keep, from the Home tab click Find & Select > Go to Special > and check Formulas. Then press delete.

 

[98apple]

Eric, thanks. Yes, tried selecting/deleting entire range, a row of data, a column of date, entire row, entire column, all rows, all columns, same in VBA using both Delete and ClearContents. Sigh. Problem is not that it won't or can't do it. It can do it. A single cell can be deleted or cleared, it just takes a long time. A single row of six cells even can be deleted (takes a loooong time). Beyond that it never has returned so I presume it can but maybe not in my lifetime. This is very weird because the number of dependent cells for these is zero!

 

[tonyyy]

98apple,

So it seems you're very much memory bound. In a similar situation (albeit with a much smaller data set) it was impossible to do anything directly to the sheet. I found that reading a range into an array, manipulating the array then writing it back actually worked.

You might consider trying...

Sub ClearRange()
    Dim arr() As Variant
    ReDim arr(1 To 222831, 1 To 6)
    Range("DWS649:DWX223479") = arr
    MsgBox "hooray!"
End Sub

The above just writes a blank array into the given range. If this too goes into never, never land you might try breaking up the range into smaller increments...

Sub ClearRange2()
Dim arr() As Variant
Dim startRow As Long, endRow As Long, i As Long
Application.ScreenUpdating = False
startRow = 649
endRow = 1000
i = 0
ReDim arr(1 To 1000, 1 To 6)

Do
    Range("DWS" & startRow + i & ":DWX" & endRow + i) = arr
    i = i + 1000
    If endRow + i > 223479 Then Exit Do
Loop
MsgBox "hooray!"
End Sub

Good luck!

 

[98apple]

tonyyy thanks, creative, tried both and others but it hung same way no matter what method deletes/clears/overwrites/etc.

Even threw more physical memory at it (still thrashed).

"Solution", if one can call it that with a straight face, was delete the entire offending tab/sheet, painfully recreate it.
Ctrl-' (the ` is lower case on the tilde ~ key) displays sheet formulas invaluable for copy without dragging references.

Horrible side effect, reason unknown, the entire VBA project and the sheet's link to VBA vanished. Restored code from backup, recreated link, but perhaps a clue.

Still the book is big and slow but now works. Thanks again.

Other clues even limited use of volatile functions greatly exacerbate things. Especially as you get closer to the bleeding edge of Excel's capabilities. Wish I knew definitively which one set it off.

 

[tonyyy]

98apple - Thanks for the update. Glad you found a "solution"...