How can I optimize this code?

[Gustaf]

In my VBA app, all input data needs to be removed occasionally in a reset procedure. The problem is that this reset procedure, which I think is quite simple and straightforward, takes several seconds to complete. I'd like to hear some ideas on what it is in the code below that is so expensive performance-wise.

Many thanks,

Gustaf

--

' Resets a sheet by clearing data on rows with known control characters
Private Sub ResetSheet(wsh As Worksheet)

Dim iCol As Integer
Dim iRow As Integer
Dim iLastRow As Integer
Dim iFirstCol As Integer
Dim iLastCol As Integer
Dim iCtrlCol As Integer
Dim sArray() As String
Dim i As Integer

sArray = Split(conCtrlStrings, " ")

' Find delimiting cells
iLastRow = wsh.Cells(wsh.Rows.Count, "H").End(xlUp).Row
iFirstCol = 41
iLastCol = wsh.Cells(2, wsh.Columns.Count).End(xlToLeft).Column + 11
iCtrlCol = 8

' Loop through rows
For iRow = 6 To iLastRow
' Loop through control characters
For i = 0 To UBound(sArray)
' If the Ctrl column on the current row matches a known Ctrl character
If Cells(iRow, iCtrlCol) = sArray(i) Then
' Clear this row
For iCol = iFirstCol To iLastCol
Cells(iRow, iCol).ClearContents
Cells(iRow, iCol).ClearComments
Next iCol
' Skip to next row
Exit For
End If
Next i
Next iRow

End Sub

 

[Bob Flanagan]

Two quick ways to improve:

Turn off screen updating with

Application.Screenupdating = false

and turn off calculation with

Application.Calculate = xlManual

You will need to turn calculation back to automatic when done.

Lastly, the article at

http://blogs.msdn.com/excel/archive...stest-way-to-scan-a-large-range-in-excel.aspx

may also be an approach that may help. But try the first two first. The
speed increase from them may be enough.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Joel]

try this change
from
For iCol = iFirstCol To iLastCol
Cells(iRow, iCol).ClearContents
Cells(iRow, iCol).ClearComments
Next iCol
to
set ClearRange = Range(cells(iRow,iFirstCol),cells(irow,iLastCol)
ClearRange.ClearContents
ClearRange.ClearComments

I think the Clear comments is what slows the code down significantly.

 

[Gustaf]

set ClearRange = Range(cells(iRow,iFirstCol),cells(irow,iLastCol)
ClearRange.ClearContents
ClearRange.ClearComments

I think the Clear comments is what slows the code down significantly.

That was it! Together with Application.Screenupdating = False, it now clears sheets almost instantly!

Gustaf