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

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

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.

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

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.)