As rows on Sheet1 increase, loading the app slows down...

[chazrab]

...on 1/1/year of each year Sheet1 is blank, with only formulas in cols E& F. The app flies when opening it.

Now - 09/28/17, Sheet1 has 890 rows of data from cols A:F. At this point in time, the app takes too long to finish loading. By EOY, I will have 1400 rows of data filled on Sheet1 and the app will take even longer to load.

I'm hesitant to put Sheet1 in a new workbook and link it back to the core app because there are Autofilter macro reports generated using Sheet1 and linking to an external workbook would to me, cause all sorts of problems. So I'd like to keep Sheet1 contained in the core app, if there's a workaround that would allow faster loading performance as Sheet1's row data grows

Does anyone know of a way to keep Sheet1 within the core app, and still have the core app load quickly as Sheet1's rows of data increase with time?

There must be others who've had this same performance loading issue as a sheet data grows.

Thanks out there for any help and suggestions on this.

cr

 

[SpillerBD]

Re: ,,,as rows on Sheet1 increase, loading the app slows down...

What kind of formulas are in use?
Are there any macros (event driven) that in use?

 

[chazrab]

Re: ,,,as rows on Sheet1 increase, loading the app slows down...

What kind of formulas are in use?
Are there any macros (event driven) that in use?

very elementary and simple ones

COL I: =IF(+A3<>"",I2-F3+H3, "")
COL J=IF(+B3<>"",J2-E3-F3+H3, "")
all the way down to about row 2000

no event driven macros. As you can tell, this is just emulates a simple register sheet.

 

[SpillerBD]

Re: ,,,as rows on Sheet1 increase, loading the app slows down...

hmm. Yeah, that function shouldn't affect time on Excel desktop. I would get rid of the "+" in the logic evaluation as it does nothing but make for an additional dot to be processed.
Are there other sheets with dependent calculations to the sheet in the workbook?

 

[chazrab]

Re: ,,,as rows on Sheet1 increase, loading the app slows down...

hmm. Yeah, that function shouldn't affect time on Excel desktop. I would get rid of the "+" in the logic evaluation as it does nothing but make for an additional dot to be processed.
Are there other sheets with dependent calculations to the sheet in the workbook?

Nope...

 

[SpillerBD]

Re: ,,,as rows on Sheet1 increase, loading the app slows down...

Conditional formatting? (I forget to ask as I hardly ever use it.)

 

[chazrab]

Re: ,,,as rows on Sheet1 increase, loading the app slows down...

Conditional formatting? (I forget to ask as I hardly ever use it.)

Yes. Rows 2 -1500 have an alternating fill color of light blue. I just used

=MOD(ROW(),2)=1

to fill this range rapidly.
How would that slow down the loading speed ?

 

[SpillerBD]

Re: ,,,as rows on Sheet1 increase, loading the app slows down...

Excel must perform the evaluation each time.
It is better to format as a Table and use one of the stripped formatting (like the default.)