Does it actually have speed problems?
It does not yet set itself to “Manual Calculation” but I did get the spinning wheel of death when trying to close out of it just now (I had both the old & new versions up. Closing out of the old version (even though it was “read only”) caused Excel to freeze, presumably crash, and then open “recovered” workbooks)…and I’ve noticed that the “Calculating (4 threads) xx%” message comes up when I make a cell change now.
Oof. Okay. Well I think I need more info.
Are you using a pentium or a 486?
More seriously, a few sumifs of 100k cells should take no time at all.
Could be more of an issue if you have 100k sumifs of 100k cells.
Do you have 100k sumifs? If so that’s the problem you need to tackle.
Again, the main thing is to find the botteneck. There is probably just one formula in your workbook causing all of the problems.
Also, check how many cells are in the “memory range” i.e., where is the rightest and downest cell (End-Home) in each tab. Sometimes these are very far away from the actual used cells when you inherit someone else’s files.
Also, the number of SUMIF’s is more important than the length of the SUMIF ranges. (Well, the combination is devastating.)
Also, look for hidden tabs.
Here’s a good resource for VBA timers: https://www.thespreadsheetguru.com/the-code-vault/2015/1/28/vba-calculate-macro-run-time
I’ve also found some spreadsheets had tons of hidden name ranges, which can bog things down. You have to be careful though, because some of these named ranges are used by Excel and the worksheet will become corrupted if you delete them.
Here’s the macro I use to find out if there are named ranges I’m not using:
Sub Show_NamedRanges() 'Show all named Ranges
Dim sh As Worksheet
Dim nm As Name
AddSheetIfMissing ("NamedRanges")
Sheets("NamedRanges").Range("A:D").Clear
Set sh = Sheets("NamedRanges")
sh.Range("A1") = "Name"
sh.Range("B1") = "Refers To"
sh.Range("C1") = "Visible"
On Error Resume Next
For Each nm In Names 'Loop through all sheet names.'
sh.Range("A" & Rows.Count).End(xlUp)(2) = nm.Name
sh.Range("B" & Rows.Count).End(xlUp)(2) = "'" & nm.RefersTo
sh.Range("C" & Rows.Count).End(xlUp)(2) = nm.Visible
sh.Range("D" & Rows.Count).End(xlUp)(2) = nm.Value
Next nm
On Error GoTo 0
End Sub
Function AddSheetIfMissing(Name As String) As Worksheet
On Error Resume Next
Set AddSheetIfMissing = ActiveWorkbook.Worksheets(Name)
If AddSheetIfMissing Is Nothing Then
Set AddSheetIfMissing = ActiveWorkbook.Worksheets.Add
AddSheetIfMissing.Name = Name
End If
End Function
1 Like