Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I optimize this code?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hou would you optimize this code? | Excel Programming | |||
Optimize SumProduct | Excel Discussion (Misc queries) | |||
Optimize SumProduct | Excel Worksheet Functions | |||
How to optimize and improve that code ? | Excel Programming | |||
How can I optimize this code? | Excel Programming |