OWC11 Performance and cell merging.

[Aloysius]

Using Range.Merge(ref bool) or Range.set_MergeCells(bool) adversely affects
performance of OWC11.

I have set
Workbook.EnableEvents = false
Workbook.EnableUndo = false
Workbook.Calculation = XLCalculation.xlCalculationManual.

I have tried merging cells in a row from left-to-right, and right-to-left.
I've tried merging the cells empty, and then populating them, and vice versa.
But all to no avail.

Does anyone know how to make cell merging work at a reasonable speed in OWC11?

A

 

[vapor]

Restrict the merge to the usedRanges instead of the entire workbook. See if
that helps.

--
--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99

 

[Aloysius]

Alvin,

I'm not sure I understand. The merges are performed on very small ranges
(two, sometimes three cells, but always within a single row). How do I
exclude this from affecting the rest of the worksheet?

Regard
A

Restrict the merge to the usedRanges instead of the entire workbook. See if
that helps.

--
--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99

Using Range.Merge(ref bool) or Range.set_MergeCells(bool) adversely
affects
performance of OWC11.

I have set
Workbook.EnableEvents = false
Workbook.EnableUndo = false
Workbook.Calculation = XLCalculation.xlCalculationManual.

I have tried merging cells in a row from left-to-right, and right-to-left.
I've tried merging the cells empty, and then populating them, and vice
versa.
But all to no avail.

Does anyone know how to make cell merging work at a reasonable speed in
OWC11?

A

 

[Alvin Bruney [ASP.NET MVP]]

One thing that 'could' be happening is that the operation applies to the
entire spreadsheet and not just the changed cells. When you use the
usedRange method, it forces the spreadsheet to only examine the changes. You
can determine if that is the issue or not by first getting a usedRange
object and then getting the merge cells from that used range.

--
--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99

Alvin,

I'm not sure I understand. The merges are performed on very small ranges
(two, sometimes three cells, but always within a single row). How do I
exclude this from affecting the rest of the worksheet?

Regard
A

Restrict the merge to the usedRanges instead of the entire workbook. See
if
that helps.

--
--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99

Using Range.Merge(ref bool) or Range.set_MergeCells(bool) adversely
affects
performance of OWC11.

I have set
Workbook.EnableEvents = false
Workbook.EnableUndo = false
Workbook.Calculation = XLCalculation.xlCalculationManual.

I have tried merging cells in a row from left-to-right, and
right-to-left.
I've tried merging the cells empty, and then populating them, and vice
versa.
But all to no avail.

Does anyone know how to make cell merging work at a reasonable speed in
OWC11?

A

 

[Aloysius]

Thanks for the suggestion. However, usedRange cell-merging is just as slow
as worksheet cell-merging. I think MS uses very inefficient
code/data-structures to shunt cells around within a sheet, when catering for
cell-mergers. I shall just have to be more imaginative about how I arrange
the data, to get rid of the need for cell merging.

 

[shahramdelta]

Using Range.Merge(ref bool) or Range.set_MergeCells(bool) adversely affects
performance of OWC11.

I have set
Workbook.EnableEvents = false
Workbook.EnableUndo = false
Workbook.Calculation = XLCalculation.xlCalculationManual.

I have tried merging cells in a row from left-to-right, and right-to-left.
I've tried merging the cells empty, and then populating them, and vice versa.
But all to no avail.

Does anyone know how to make cell merging work at a reasonable speed in OWC11?

A

Hi
Do as follows:
suppose you want to merge cells in two column cells from let say B2 to ZY100, for example B,C D,E F,G each one merged cell and so on

let say "ss" is the sheet name
ss.range.merge("B2","C2")
ss.range("B2", "C2").copy ss.range("D2","ZY2")
ss.range("B2","ZY2").copy ss.range("B3","ZY100")

it is fast