Subtotalling taking long time to complete

[AndyV]

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?

 

[Pete_UK]

Instead of using the Data | Subtotals routine, for which your data
must be sorted beforehand, and then copying the values to another
sheet, you can set up some SUMIF formulae in the second sheet to give
you the same as the subtotals you currently have. Just list the values
of the field that you want the subtotal for in column A, and then a
formula like:

=SUMIF(Sheet1!ref_col,A1,Sheet1!G:G)

and:

=SUMIF(Sheet1!ref_col,A1,Sheet1!Q:Q)

will give you the equivalent, where ref_col will be the column you
generate the subtotals from, eg C:C. Copy these down as required.

Hope this helps.

Pete

 

[AndyV]

Thanks Pete,

The problem is that I'm using both the sub-total values and the rows on
which they appear to determine what data I need to copy and manipulate.

I thought there may be some issue regarding undo. I've read an MS forum
where the undo command is maintained both within macros and when the file is
saved, bBut that it can be cleared from within a macro. I thought Excel may
have a large undo list of actions that it was trying to maintain.

 

[Dave Peterson]

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]

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