subtotaling becomes slow
 

Hello,

I use the subtotal function freqeuntly in VB code. The first time I run the
code with this function it works very fast. However, the more I use this
macro, the slower and slower it gets. If I shut down Excel and restart, it
is fast again. It is a very simple macro with no variables to set or clear.
Is there anything I can do to prevent it from becoming slow (clear some cache
somewhere or something)?

Thanks

Below is the code:

Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

subtotaling becomes slow
 

When you see those dashed lines that show page breaks, then excel will think
that it has to figure out where they should be placed when you insert/delete
rows or columns.

I think I'd try this at the top of the code:

ActiveSheet.DisplayPageBreaks = False

Then your code.

If that doesn't help, maybe you could turn calculation to manual, add the
subtotals, then turn it back to automatic.

That could help, too.

nathan wrote:

Hello,

I use the subtotal function freqeuntly in VB code. The first time I run the
code with this function it works very fast. However, the more I use this
macro, the slower and slower it gets. If I shut down Excel and restart, it
is fast again. It is a very simple macro with no variables to set or clear.
Is there anything I can do to prevent it from becoming slow (clear some cache
somewhere or something)?

Thanks

Below is the code:

Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

--

Dave Peterson

subtotaling becomes slow
 

Thanks for your response. That didn't seem to help. I already have the calc
turned off. Just seems to be some memory leak, wish there was a way to
retrieve it. This problem exists even when I do a manual subtotal from the
menu list data/subtotal. Removing the subtotal is just as slow... thanks
again.

"Dave Peterson" wrote:

When you see those dashed lines that show page breaks, then excel will think
that it has to figure out where they should be placed when you insert/delete
rows or columns.

I think I'd try this at the top of the code:

ActiveSheet.DisplayPageBreaks = False

Then your code.

If that doesn't help, maybe you could turn calculation to manual, add the
subtotals, then turn it back to automatic.

That could help, too.

nathan wrote:

Hello,

I use the subtotal function freqeuntly in VB code. The first time I run the
code with this function it works very fast. However, the more I use this
macro, the slower and slower it gets. If I shut down Excel and restart, it
is fast again. It is a very simple macro with no variables to set or clear.
Is there anything I can do to prevent it from becoming slow (clear some cache
somewhere or something)?

Thanks

Below is the code:

Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

--

Dave Peterson