2007 Merge cells taking forever

[J Streger]

It seems that every time I try to merge cells via VBA, it takes forever. Code
that ran in 1 second in 2003 is taking over 10 seconds in 2007, and Excel is
stating it is Not Responsive so even the status bar is not updating. I tried
to pull all of the merging out of my loop and do it in one line, thinking
that this would be faster, but alas it seems the merging time is proportonal
to the amount of sections I'm merging. Anyone else have this issue and know
how to fix?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

 

[Joel]

See posting below. Microsoft is look for sample of macros that is running
slower on 2007 than 2003.

http://www.microsoft.com/office/com...&p=1&tid=b8717b84-8696-49bd-b230-1a40da311c6d

 

[Peter T]

Just curiosity, is calculation dis/enabled during the process

Regards,
Peter T

 

[Rick Rothstein]

Can you post your code so we can test it on different systems to see if it
is universal or operating system dependent? I'm using Vista myself, but I
know others are still using XP, 2000, etc. Also, which operating system are
you using?

 

[J Streger]

It is turned off. I run this method at the start of every entry point:

Public Sub ScreenUpdating(Optional bEnable As Boolean = True)

Application.ScreenUpdating = bEnable
Application.Calculation = IIf(bEnable, xlCalculationAutomatic,
xlCalculationManual)
Application.Interactive = bEnable
Application.EnableEvents = bEnable
ThisWorkbook.Running = Not bEnable

End Sub 'ScreenUpdating


--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

 

[J Streger]

Thanks. I guess I will submit my code to them. I really want this issue fixed.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003

 

[Peter T]

Thanks. I forgot to ask last time but are you using Vista.

For your purposes you shouldn't need to disable calculation unless anything
you are doing triggers or requires a recalc. Typically if merely merging
cells that's not the case. Though If you are doing very many merges you
might want to disable screenupdating.

Regards,
Peter T

 

[J Streger]

I actually have determined what the slow down was.

I did test this on both Excel 2003 on a vista machine, and Excel 2007 on
both a Vista and an XP machine. It is definitely a problem with 2007.

I can tell you that this block of code:

With rngMergeInfo
.Merge
.Font.Italic = True
.Font.Size = 10
.VerticalAlignment = xlCenter
End With

took 13 seconds to process. But what I failed to mention is that the sheet
has a lot of small .png pictures. They are almost all hidden and I unhide
them when needed. Now in 2003 the code had no problem, but there must be a
major difference with how 2007 works with hidden pictures as when I deleted
them all and ran the code, there was no slowdown.

Now I positioned the pictures with their left in the right spot so that I
wouldn't have to set the left property programmatically. So I'm hoping if I
move the pictures to a part of the sheet that won't have cells merged, that I
won't have the slowdown.

Any thoughts on how Excel handles the pictures differently?


--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003