Turning off calculations not preventing hanging

[JIMBO992]

Hi all,

I have the below formula in approximately 1.4M cells (192 columns by 8036 rows) in one worksheet:

=IFERROR(IF(GP$119<$M124,"",IF(GP$119=$M124,HLOOKUP("T0",$119:$10231,MATCH($N124,$N$119:$N$10231,0),0),HLOOKUP("T+"&DATEDIF($M124,GP$119,"M"),$119:$10231,MATCH($N124,$N$119:$N$10231,0),0))),0)

In the same worksheet I also have an additional 150 columns (also with 8036 rows) that have far fewer non blank cells (205k). 103k of those cells have the below formula, with the remaining 102k being hard coded values:

=IF(OR(EE7579="",EE7579=0),"",VLOOKUP($V7602,$C$1:$D$30,2,0)*EE7579)

The document has become very slow and hangs and/or crashes when attempting to manipulate/change the sheet, such as inserting columns/rows (even in blank cells outside of any data); Sorting; cutting and pasting etc.

I was hoping that running a simple piece of VBA code (below), to turn off calculations would prevent the root cause of the hanging:

Sub Turn_off_calculations()

Worksheets("Sheet 1").EnableCalculations = False

End Sub

Calculations are turned off, but even if I attempt to insert a single row outside of my data range, excel still hangs/takes a long time to insert row.

Should disabling calculations prevent excel from hanging?

Many thanks in advance for any assistance!

James.

 

[rlv01]

You might be better off using

    Application.Calculation = xlCalculationManual
    Application.Calculation = xlCalculationAutomatic

 

[JIMBO992]

Thanks for the response and input, riv!

I should have clarified, manual calculations was the first step I took, but that doesn't prevent the hanging. From reading historic issues from others, most people believe it should?!

James.

 

[rlv01]

Yes it should if the formulas are the cause of the hanging. But there are other things that can cause Excel to hang. Poorly designed event macro code or lack of sufficent available PC memory to name two.

 

[Fluff]

A lot of conditional formatting can cause problems & is not affected by turning calculation to Manual