Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Merge cells taking forever
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Merge cells taking forever
See posting below. Microsoft is look for sample of macros that is running
slower on 2007 than 2003. http://www.microsoft.com/office/comm...0-1a40da311c6d "J Streger" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Merge cells taking forever
Just curiosity, is calculation dis/enabled during the process
Regards, Peter T "J Streger" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Merge cells taking forever
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? -- Rick (MVP - Excel) "J Streger" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Merge cells taking forever
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 "Peter T" wrote: Just curiosity, is calculation dis/enabled during the process Regards, Peter T "J Streger" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Merge cells taking forever
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 "Joel" wrote: See posting below. Microsoft is look for sample of macros that is running slower on 2007 than 2003. http://www.microsoft.com/office/comm...0-1a40da311c6d "J Streger" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Merge cells taking forever
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" wrote in message ... 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 "Peter T" wrote: Just curiosity, is calculation dis/enabled during the process Regards, Peter T "J Streger" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Merge cells taking forever
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 "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "J Streger" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Taking away from stock in excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 conditional format and merge cells | Excel Discussion (Misc queries) | |||
Excel File Taking Forever to Open | Excel Discussion (Misc queries) | |||
Calculating Cells Taking Forever | Excel Discussion (Misc queries) | |||
mail merge- & data source - only taking 1st name in the list | Excel Discussion (Misc queries) |