How can I optimize this code?

G

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
 
B

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
 
J

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.
 
G

Gustaf

Joel said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top