cplindem
04-11-2014, 11:50 AM
I have a large subroutine that formats Excel workbooks. The entire subroutine usually takes ~30 seconds to run on 40,000 rows of data. However, it sometimes decides to take ~3 minutes to run. It seems completely random to me. I tried to isolate a snippet of code that might be causing the variance in duration, by using "Stop". The culprit seems to be the loop in this section:
Dim FoundCell As Range
Dim FirstAddr As String
Dim LastCell As Range
Dim LastRow As Long
LastRow = Range("A1048576").End(xlUp).Row
With Range("A1:A" & LastRow)
Set LastCell = .Cells(LastRow)
End With
Set FoundCell = Range("A1:A" & LastRow).Find(what:="Total:", After:=LastCell, MatchCase:=True, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
End If
Stop '(lots of code comes before this). This is the loop that sometimes takes forever to run through:
Do Until FoundCell Is Nothing
With FoundCell
With .EntireRow
.Font.Size = 8
.Font.color = Cells(1, 8).Interior.color
.NumberFormat = "#,##0"
End With
.Offset(0, 1).NumberFormat = """n=""#,##0"
End With
Set FoundCell = Range("A1:A" & LastRow).FindNext(After:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do
End If
Loop
Stop '(lots of code comes after this)
Any ideas why this section might run quickly sometimes, slowly at others? This variance occurs without me changing anything in the code. The data that I am testing the code on also never changes.
Dim FoundCell As Range
Dim FirstAddr As String
Dim LastCell As Range
Dim LastRow As Long
LastRow = Range("A1048576").End(xlUp).Row
With Range("A1:A" & LastRow)
Set LastCell = .Cells(LastRow)
End With
Set FoundCell = Range("A1:A" & LastRow).Find(what:="Total:", After:=LastCell, MatchCase:=True, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
End If
Stop '(lots of code comes before this). This is the loop that sometimes takes forever to run through:
Do Until FoundCell Is Nothing
With FoundCell
With .EntireRow
.Font.Size = 8
.Font.color = Cells(1, 8).Interior.color
.NumberFormat = "#,##0"
End With
.Offset(0, 1).NumberFormat = """n=""#,##0"
End With
Set FoundCell = Range("A1:A" & LastRow).FindNext(After:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do
End If
Loop
Stop '(lots of code comes after this)
Any ideas why this section might run quickly sometimes, slowly at others? This variance occurs without me changing anything in the code. The data that I am testing the code on also never changes.