Thanks Dave,

I now appreciate that subtotals seem system intensive and there are better
ways but all my code in this sheet is based around them. I'll add your lines
to improve the general performance.

Its messy to explain what I'm doing, but I've created subtotals above the
detail rows which I compare to a cell in the first of the subsequent details
rows (which contains what should be the correct total). If there is a
difference, I add this difference to the first detail line, then move on down
to the next sub total line. At the end of this process, the total of the
details matches the total that is held on the detail line. (I'm trying to get
around some vat rounding issues on a data export).

I'm using excel 2007, but ported part of the code using compatibility mode
and subtotals take minutes, so it seems to be a problem with 2007. I've
decided to try and create and manipulate the data by creating a new workbook
on every occasion I create an export file.

Andrew

"Dave Peterson" wrote:

If you're copying the subtotal rows (not the details), then I bet that creating
a pivottable would be quicker than subtotals, copy|pasting, removing subtotals.

But if you want to use subtotals (I wouldn't!)...

I'd turn calculation to manual, then do the work, then change the calculation
back to whatever it was before.

In fact, there are a few things that can slow down macros. I do this kind of
thing to speed them up:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Your code will replace the "'do the work" line.

When/if you're hiding rows/columns, excel wants to figure out where to draw
those lines each time you change the layout.


AndyV wrote:

I have a spreadsheet with roughly 2000 rows and 18 columns. As part of a
macro, I sub total columns 7 and 17. I use a macro to copy select cells into
a second sheet.

After this, I remove the subtotals. I use lookups from the second sheet into
the first sheet. As part of my tests, I have since copied and pasted the
lookup cells as absolute values. There are no longer any lookups in any sheet.

The initial subtotal and it's removal, when creating the macro, took seconds
to complete, however on subsequent attempts, they take hours.

I have since copied and pasted the sheet into a different workbook, and the
subtotalling function in the second workbook takes seconds to complete.

What can I do to improve the performance?

--

Dave Peterson