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

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

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