PDA

View Full Version : Code duration inconsistent



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.

SamT
04-11-2014, 03:22 PM
See if this runs faster on the slow worksheets

Do Until FoundCell Is Nothing
'Optimized for speed
With FoundCell.EntireRow
NewColor = .Cells(8).Color '<--- Be sure to Dim NewColor above
With .Font
.Size = 8
.Color = NewColor
End With
.NumberFormat = "#,##0"
End With
FoundCell.Offset(0, 1).NumberFormat = """n=""#,##0"
End With
Set FoundCell = ...

cplindem
04-11-2014, 04:32 PM
Thank you. With a couple modifications (you had an extra 'End With' and you missed '.Interior' before the color) that did shave a few seconds off the total duration.

However, the issue isn't really the total duration per se. The issue is the total duration varies. I don't mean to say that it varies from worksheet to worksheet. I have been testing it on the exact same worksheet. Sometimes the total duration is ~30 seconds, sometimes a few minutes. ...on the same worksheet. Basically I'm changing nothing, but the duration will randomly go on for a few extra minutes longer for seemingly no reason.

GTO
04-11-2014, 05:10 PM
If you could substitute same data types for any sensitive data and post the wb, so we could see what is really in action, that might be helpful.

cplindem
04-11-2014, 05:18 PM
I don't have anymore time today, but perhaps on Monday.

Thanks all