Slow Load Help

[Joe S]

I use automation (from C#) to generate a 100+ page report. The report
includes lots of borders/lines and merged cells to make data cells in
a "pretty" report.

Originally, I created it one page per worksheet, but I found that that
inhibited duplex printing.

I then changed it to make the pages be successive areas on a single
sheet, vertically. However, now loading is VERY slow...up to a minute
to load a 100+ sheet report, though loading a 35 page report is still
pretty quick. Load time seems to grow nearly geometrically. It doesn't
seem like it should take nearly this long.

By clearing formatting for the meat of the report, eliminating the
border lines, load times seem normal.

So, any advice as to how I can have my cake (cell borders) and eat it
(load fast), too?

Thanks,


Joe

 

[Vasant Nanavati]

Are you making a lot of PageSetup calls? PageSetup is *extremely* slow in
VBA. You may need to use an XLM macro if this is the case.

 

[Joe Schey]

No, no Page Setups. And I'm using C#.

I've narrowed it down to the problem being due to the number of merged
cells, also affected by how deep into the sheet (the largeness of the
row number) they are.

Each page contains a matrix of 20 "virtual rows" (each made up of 3
Excel rows) by 8 "virtual columns" (each column being made up of around
8 Excel columns).

Each virtual cell has either one merged cell (3 rows by 8 columns) or 2
merged cells (either 1 row or 2 rows by 8 columns).

As I mentioned, if there is only one page per Worksheet, I don't run
into this problem, though there is an aggregate same number of merged
cells.

I have a sneaky feeling it has something to do with memory management
that requires a huge amount of memory within a Worksheet that is avoided
if the same data is stored in separate Worksheets.


Joe

 

[Vasant Nanavati]

I never use merged cells. They invariably cause problems, although this is a
new one on me.