Opening/Closing a lot of groupings via VBA

[Dirk Flakowski]

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]

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]

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

 

[Dave Peterson]

maybe this'll help:

XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns
http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769

(It sounds kind of like it may match your translation.)

 

[Dirk Flakowski]

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