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

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" wrote:

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

Joel wrote:

> 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