Excel macro slows down 10X with addition of data heavy sheet

[RawlinsCross]

So I have several sub procedures in an excel application, one of which uses a few For Loops to enter data into various dictionaries. In an older version this particular sub procedure took 0.102 seconds to execute. Then I added a worksheet to the workbook which contains a lot of data (number of cells containing data = 2.4 million). Now this sub procedure takes 11.52 seconds to execute even though the procedure really doesn't reference this new worksheet. The worksheet on which the For Loop references is called "DataWorkup" and this new data-heavy is called "Harvested". You can see in the code that "Harvested" is not referenced - why would this code slow down in that case?

The iteration count in these For Loops range from 7 to 12 so they're quite short. So I'm not sure why this sub-procedure would take any longer to run. Any thoughts on what I can do here?

Private Sub FileToSpreadSheet()

Dim wSht As Worksheet
Dim rRange As Range
Dim i As Long
Dim sActivity As String
Dim Add1 As Long
Dim Add2 As Long
Dim StartTime As Double
Dim SecondsElapsed As Double

StartTime = Timer

Set wSht = ThisWorkbook.Worksheets("DataWorkup")
wSht.Unprotect "Tankhouse123"

'Transfer "Crane Related-West" (GOOD)
Set rRange = wSht.Range("WestCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
rRange.Cells(i, 4).Value = moWestCraneDict(sActivity)
Next i

'Transfer "Non-Crane Related-West" (GOOD)
Set rRange = wSht.Range("WestNonCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
rRange.Cells(i, 4).Value = moWestNonCraneDict(sActivity)
Next i

'Transfer "West Both" (GOOD)
Set rRange = wSht.Range("WestBothWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
'This tag will either be in moWestCraneDict or moWestNonCraneDict
If moWestCraneDict.Exists(sActivity) Then
rRange.Cells(i, 4) = moWestCraneDict(sActivity)
Else
rRange.Cells(i, 4) = moWestNonCraneDict(sActivity)
End If
Next i

'Transfer "Crane Related-East" (GOOD)
Set rRange = wSht.Range("EastCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
rRange.Cells(i, 4).Value = moEastCraneDict(sActivity)
Next i

'Transfer "Non-Crane Related-East" (GOOD)
Set rRange = wSht.Range("EastNonCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
rRange.Cells(i, 4).Value = moEastNonCraneDict(sActivity)
Next i

'Transfer "East Both" (GOOD)
Set rRange = wSht.Range("EastBothWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
'This tag will either be in moEastCraneDict or moEastNonCraneDict
If moEastCraneDict.Exists(sActivity) Then
rRange.Cells(i, 4) = moEastCraneDict(sActivity)
Else
rRange.Cells(i, 4) = moEastNonCraneDict(sActivity)
End If
Next i

'Transfer "Both Crane" (GOOD)
Set rRange = wSht.Range("BothCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
'These tags will be in moWestCraneDict and moEastCraneDict
rRange.Cells(i, 4) = moWestCraneDict(sActivity) + moEastCraneDict(sActivity)
Next i

'Transfer "Both NonCrane" (GOOD)
Set rRange = wSht.Range("BothNonCraneWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
'These tags will be in moWestNonCraneDict and moEastNonCraneDict
rRange.Cells(i, 4) = moWestNonCraneDict(sActivity) + moEastNonCraneDict(sActivity)
Next i

'Tranfer "Both Both" (GOOD)
Set rRange = wSht.Range("BothBothWater")
For i = 3 To rRange.Rows.Count
sActivity = rRange.Cells(i, 1).Value
'This tag will either be in moEastCraneDict or moEastNonCraneDict and their West counterpoints
If moEastCraneDict.Exists(sActivity) Then
rRange.Cells(i, 4) = moEastCraneDict(sActivity) + moWestCraneDict(sActivity)
Else
rRange.Cells(i, 4) = moEastNonCraneDict(sActivity) + moWestNonCraneDict(sActivity)
End If
Next i

'Sort the ranges
wSht.Range("WestCraneWaterSort").Sort Key1:=wSht.Range("R3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("WestNonCraneWaterSort").Sort Key1:=wSht.Range("X3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("EastCraneWaterSort").Sort Key1:=wSht.Range("AM3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("EastNonCraneWaterSort").Sort Key1:=wSht.Range("AS3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("WestBothWaterSort").Sort Key1:=wSht.Range("AE3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("EastBothWaterSort").Sort Key1:=wSht.Range("AZ3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("BothCraneWaterSort").Sort Key1:=wSht.Range("BG3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("BothNonCraneWaterSort").Sort Key1:=wSht.Range("BM3"), Order1:=xlDescending, Header:=xlNo
wSht.Range("BothBothWaterSort").Sort Key1:=wSht.Range("BT3"), Order1:=xlDescending, Header:=xlNo

SecondsElapsed = Round(Timer - StartTime, 3)
Debug.Print SecondsElapsed

End Sub

 

[Fluff]

What happens if you turn calculation to manual at the start of the code?

 

[RawlinsCross]

Hi Fluff... do you mean calculation on the "DataWorkUp" page?

 

[Fluff]

Put the first of these two lines at the top of your code & the other one at the bottom

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

 

[RawlinsCross]

Of course, so yes, it did work to reduce the procedure run time. This procedure is one of many that are run in a userform_initialize. I ran time tests on all of them for each version for both xlAutomatic and xlManual. See results below.

Time in seconds	xlAutomatic		xlManual
Sub Procedure	Old Version	New Version	Old Version	New Version
InitializeControls	0.00391	0	0.00391	0
PopulateArrays	0	0	0	0
MakeDicts	0.00781	0.00781	0.00781	0.00781
TimeRange	0.00391	0	0.00391	0.00391
FindTimes	0.105	0.40234	0.105	0.39844
FileToSpreadsheet	0.21875	12.5	0.21875	0.40625
SetRanges	0	0	0	0
MakeChart	0.01562	0.01562	0.01562	0.01562
PlaceGraph	0.11328	0.11328	0.11328	0.10547
DeleteDicts	0.11328	0.11328	0.11328	0.11328

 

[Fluff]

You're welcome & thanks for the feedback

 

[RawlinsCross]

So perhaps another question on this topic. I don't understand why the inclusion of data (lots of it) on another sheet would have slowed down a FOR loop on an unrelated sheet?

 

[Fluff]

My guess is you have a lot of formulae on the other sheet that look at the data sheet.