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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I optimize this code?
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/...-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 "Gustaf" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I optimize this code?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I optimize this code?
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 |
Reply |
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 |