Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening/Closing a lot of groupings via VBA
Hello,
I have a workbook with about 10.000 rows with about 300 vertical groupings in it. In my program I have to close/open all the groupings from time to time via VBA. So I loop through all rows (1 to 10.000) and set the "ShowDetail"-property of all rows to TRUE/FALSE - ignoring the errors occuring for rows having no grouping. But this takes about 20s what is quite too long for the user to wait for. So does anybody have any idea how I could speed up opening/closing all groupings? I also have used "ActiveSheet.Outline.Showlevels" - but that is nearly the same (due to performance) and has some other problems (e.g. on closing I have to call it 8 times - once with "RowLevels:=8, once with :=7... :=1). So if you have any suggestion the please let me know. Thanks in advance. Dirk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening/Closing a lot of groupings via VBA
You're hiding all or showing all???
If yes, then how about just doing it twice: ActiveSheet.Outline.ShowLevels 1 and later: ActiveSheet.Outline.ShowLevels 8 If you wanted to just see some of the outline, I can see why you'd want to loop--but not all or one. Dirk Flakowski wrote: Hello, I have a workbook with about 10.000 rows with about 300 vertical groupings in it. In my program I have to close/open all the groupings from time to time via VBA. So I loop through all rows (1 to 10.000) and set the "ShowDetail"-property of all rows to TRUE/FALSE - ignoring the errors occuring for rows having no grouping. But this takes about 20s what is quite too long for the user to wait for. So does anybody have any idea how I could speed up opening/closing all groupings? I also have used "ActiveSheet.Outline.Showlevels" - but that is nearly the same (due to performance) and has some other problems (e.g. on closing I have to call it 8 times - once with "RowLevels:=8, once with :=7... :=1). So if you have any suggestion the please let me know. Thanks in advance. Dirk -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening/Closing a lot of groupings via VBA
Hello Dave,
thanks for your answer. With that I have one problem: All rows and columns which are not used are hidden and the worksheet contains a lot of comments. Then on using that statement I get the following error (I try to translate the German message): "Objects can't be moved over the border" So before using the statement I have to make a few rows/columns visible, execute the statement and then make them invisible again. That works fine but then it also takes quite long - especially on closing all as I have to execute the statement there 8 times (once for each level) so that really all groupings are closed. Thanks Dirk On Wed, 15 Dec 2004 20:57:28 -0600, Dave Peterson wrote: You're hiding all or showing all??? If yes, then how about just doing it twice: ActiveSheet.Outline.ShowLevels 1 and later: ActiveSheet.Outline.ShowLevels 8 If you wanted to just see some of the outline, I can see why you'd want to loop--but not all or one. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening/Closing a lot of groupings via VBA
maybe this'll help:
XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default...b;en-ca;211769 (It sounds kind of like it may match your translation.) Dirk Flakowski wrote: Hello Dave, thanks for your answer. With that I have one problem: All rows and columns which are not used are hidden and the worksheet contains a lot of comments. Then on using that statement I get the following error (I try to translate the German message): "Objects can't be moved over the border" So before using the statement I have to make a few rows/columns visible, execute the statement and then make them invisible again. That works fine but then it also takes quite long - especially on closing all as I have to execute the statement there 8 times (once for each level) so that really all groupings are closed. Thanks Dirk On Wed, 15 Dec 2004 20:57:28 -0600, Dave Peterson wrote: You're hiding all or showing all??? If yes, then how about just doing it twice: ActiveSheet.Outline.ShowLevels 1 and later: ActiveSheet.Outline.ShowLevels 8 If you wanted to just see some of the outline, I can see why you'd want to loop--but not all or one. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening/Closing a lot of groupings via VBA
Hello Dave,
yese this is the message I receive. I will try if it is a solution for me as soon as I will have time to do so. Thank you very much! Dirk On Thu, 16 Dec 2004 18:21:36 -0600, Dave Peterson wrote: maybe this'll help: XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default...b;en-ca;211769 (It sounds kind of like it may match your translation.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing a workbook upon opening another | New Users to Excel | |||
Event on opening/closing groupings in VBA | Excel Discussion (Misc queries) | |||
Opening and closing many spreadsheets from .NET | Excel Programming | |||
Opening and closing workbook with VBA... | Excel Programming | |||
Opening and Closing workbooks | Excel Programming |