Subtotal computation speed variation among separate sheets with same data

[jthompson92]

Hello,

My first post here, so let me know if you need more information. I've written a macro that calls the following function with sName = the sheet name, and group = name of the desired range to sort by:

Function g_SortByGroup(sName, group)
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim subCol1 As Long
    Dim subCol2 As Long
    Dim cName As String
    Dim ws As Worksheet
    
'Sets values using other functions
    Set ws = ActiveWorkbook.Sheets(sName)
    lastColumn = getLastColumn(sName) + 1
    lastRow = getLastRow(sName)
    cName = getColumnName(sName)
    
'Sort according to columns that will be subtotaled
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range( _
        cName & "_" & group).Rows("2:" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ws.Sort.SortFields.Add Key:=Range( _
        cName & "_Shipping_Name").Rows("2:" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ws.Sort
        .SetRange Range(Cells(2, 2), Cells(lastRow, lastColumn))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'Get the column numbers of the columns to be subtotaled
    subCol1 = getColumnNumber(sName, "_" & group) - 1
    subCol2 = getColumnNumber(sName, "_Quantity") - 1
'First subtotal
    Range(cName & "_All").Select
    Selection.Subtotal GroupBy:=subCol1, Function:=xlSum, TotalList:=Array(subCol2), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
'Get new values
    subCol1 = getColumnNumber(sName, "_Shipping_Name") - 1
    lastRow = Application.WorksheetFunction.CountA(Range(cName & "_Quantity"))
'Second subtotal
    Range(cName & "_All").Select
    Selection.Subtotal GroupBy:=subCol1, Function:=xlSum, TotalList:=Array(subCol2), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=3
End Function

I have two essentially identical sheets - one is current inventory and the other is inventory archive. Currently, in the testing phase, each sheet is absolutely identical. They both contain the exact same data and zero formulas. For some reason, when I execute the macro on the archive sheet, it computes in seconds, while in the inventory sheet, it takes minutes, and the computation is risky for crashing.

Does anyone know of a possible reason for this? I should mention that another sheet has several VLOOKUP references to the current inventory sheet, but removing these doesn't seem to make a difference.

Thank you

 

[StephenCrump]

Welcome to the Forum!

I'd start by trying to narrow down where the code is running slowly - by putting in a few breakpoints and running sections of the code using F5, or by using VBA's timer function.

Potentially there are many possibilities, e.g. the problem might be in some of the code you haven't posted, or in other event code being triggered?

Alternatively, if you are able to post your workbook (?) we could try to replicate the problem.

 

[jthompson92]

Hi Stephen,

Thank you for your reply. I tried running through the code with F8 on unsorted versions of both the 'Archive Rec' sheet that runs smoothly, and the 'Inventory' sheet that computes slowly. The sorting and all variable assignments are instant. The line that causes the slow computations is the first Selection.Subtotal method call.

I have a feeling the issue is occurring as a result of having complicated named ranges, however both of these sheets have identical named ranges, so the difference in speed is not immediately clear.
82 named ranges across the entire workbook, with the most complex being the x_All ranges:
i_All = =Inventory!$B$2:INDEX(Inventory!$1:$1048576,MATCH(9.99999999999999E+307,Inventory!$H:$H),COUNTA(Inventory!$2:$2)+1)
ar_All = ='Archive Rec'!$B$2:INDEX('Archive Rec'!$1:$1048576,MATCH(9.99999999999999E+307,'Archive Rec'!$H:$H),COUNTA('Archive Rec'!$2:$2)+1)

I'm thinking that each time a subtotal is added (e.g. a new row) all of my named ranges update and cause long computation times.

A question for you: would it be wise to abandon my subtotal functions and instead implement pivot tables? Are pivot tables inherently faster? I prefer the look of subtotals, but in the end that's something I'd be willing to sacrifice. Once we begin using this inventory management system, the data sets will become increasingly large and these speed issues could cause headaches down the line.

Thanks again!

 

[StephenCrump]

So a single line (the first .Subtotal) runs some orders of magnitude slower for the inventory sheet compared to the archive sheet? And on the face of it, the two sheets are identical?

You might need to do a bit more detective work. In cases like this, I tend to start stripping and simplifying (a test copy of) the workbook to isolate the problem. You're best placed to understand the workbook and how you might do this, but perhaps you might try:

- Replacing all formulae in the inventory sheet with values? Could there be rogue formula/e increasing the calculation time for one sheet?
- Replacing your dynamic ranges with hard-coded ranges - I don't think this will be the problem, but it's worth testing.
- Removing other sheets to remove dependencies as a source of slow calculation.
- ???