Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FindFormat
I don't know what else you have in your code, but one of
the main reasons a loop takes long to run is that everytime you change what is in a cell, everything dependant on that cell recalculates. So to speed up any piece of program code, add this before the loop. Application.Calculation=xlCalculationManual Then once all is done, Application.Calculation=xlCalculationAutomatic Another thing that slows things down is the screen updating. So one more thing to speed up operation: Application.ScreenUpdating = False at the beginning of a routine, and Application.ScreenUpdating = True at the end. Use both of these and your code could execute 1000 times faster. -----Original Message----- Hello everyone I have written some code to change green cells in which formulas have been entered into values (= PasteSpecial Paste:=xlPasteValues). The only problem is, this code takes very long to execute, since it is doing the operation cell for cell. Is is possible to address the entire range of green cells in a sheet, and to do a copy and pastespecial (the cells are not necessarily in one block)? Any help to speed this up would be appreciated. On Error Resume Next Application.FindFormat.Clear Application.FindFormat.Interior.ColorIndex = 35 Set celltofind = Cells.Find(what:="", searchformat:=True) firstcell = celltofind.Address celltofind.Value = celltofind.Value Do Set celltofind = Cells.Find(after:=celltofind, what:="", searchformat:=True) celltofind.Value = celltofind.Value Loop While Not celltofind.Address = firstcell Application.FindFormat.Clear . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FindFormat with Borders not Working | Excel Discussion (Misc queries) |