Speed of calculations Depends on ScrollRow

Tested further ... Here is the relevant code from the Workbook_SheetChange event

fWShChange = True ' Ensures Events are looped out, in case some part of the extensive code sets EnableEvents to True
CloseScreen ' Locks the screen and updating
fProtect = True
Z = ActiveWindow.ScrollRow
PagesProtect False, False ' unprotects relevant pages
ActiveWindow.ScrollColumn = 1 ' Tried changing this to various values -> no change in timing
ActiveWindow.ScrollRow = Y ' When this is changed to any value higher than last row there is no difference


DoCalc Target ' The main algorithm


fProtect = False
DoEvents
PagesProtect , False ' reprotects the relevant pages
fWShChange = False ' Allows events codes to function
ActiveWindow.ScrollRow = Z
ActiveWindow.ScrollColumn = 1 ' Irrelevant
oTM = Now + 0.0000002
Application.OnTime oTM, "openscreen", , True ' Allows screenupdating and opens the window
--------------------

Reduced the amount of input data to speed up the calculations (only a few rows of the 45 used)
With Y set to 1 to 36, Consistently get .641 ~ .656
With Y set to 37 to 57, Consistently get 1.515 ~ 1.563
With Y set to 58, 100, 200, Consistently get .688 ~ 735
With Y set to 1000, Consistently get .688 ~ 735
With Y set to 10000, No change from 1000
With Y set to 30000, No change from 1000

As mentioned above, changing the ScrollColumn does not seem to have any effect.
Rows 58 onwards are hidden and have no data that is part of the manual data-entry but have constants or True / False values in rows 100-160 in column C that are used in the DoCalc algorithm.

It is certain that ScrollRow of 37 to 57 causes a substantial slowdown in calculation speed.

While, it could be 'just with my workbook', it still remains an unexpected behaviour of Excel and this post may be helpful to others, I hope.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top