Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing a macro for speed- find and replace
Hey all, Thanks for your help. I have a workbook with 2 sheets with 24,000 rows and 10 columns o data, and I need to do a find and replace for all "#Missing" values an return an empty cell. This takes forever. Is there anyway to optimiz the macro to run much faster? Application.ScreenUpdating = False With Application .Calculation = xlManual End With Sheets("Version 1").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByColumns ActiveWorkbook.PrecisionAsDisplayed = False With Application .Calculation = xlAutomatic .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Application.ScreenUpdating = Tru -- trickdo ----------------------------------------------------------------------- trickdos's Profile: http://www.excelforum.com/member.php...nfo&userid=940 View this thread: http://www.excelforum.com/showthread.php?threadid=27756 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing a macro for speed- find and replace
What speed is your computer? I tried the following code on a sample
workbook that I filled in 24,000 rows of data and 10 columns and randomly blanked out some of the cells Public Sub deBlank() Application.ScreenUpdating = False Application.EnableEvents = False Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByColumns Application.EnableEvents = True Application.ScreenUpdating = True End Sub This took about 2 seconds to complete. Is there something else going on you are not telling us? Are there calculations occuring that are taking additional time? Paul D "trickdos" wrote in message ... Hey all, Thanks for your help. I have a workbook with 2 sheets with 24,000 rows and 10 columns of data, and I need to do a find and replace for all "#Missing" values and return an empty cell. This takes forever. Is there anyway to optimize the macro to run much faster? Application.ScreenUpdating = False With Application Calculation = xlManual End With Sheets("Version 1").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByColumns ActiveWorkbook.PrecisionAsDisplayed = False With Application Calculation = xlAutomatic MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Application.ScreenUpdating = True -- trickdos ------------------------------------------------------------------------ trickdos's Profile: http://www.excelforum.com/member.php...fo&userid=9405 View this thread: http://www.excelforum.com/showthread...hreadid=277560 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing a macro for speed- find and replace
Maybe OP has Iteration ON... (as he's setting the MaxChange...) See tools/options/Calculation... DEACTIVATE the iteration checkbox.. (which is ONLY usefull for certain specific recursive or circular calculation scenarios) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam PaulD wrote : What speed is your computer? I tried the following code on a sample workbook that I filled in 24,000 rows of data and 10 columns and randomly blanked out some of the cells Public Sub deBlank() Application.ScreenUpdating = False Application.EnableEvents = False Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByColumns Application.EnableEvents = True Application.ScreenUpdating = True End Sub This took about 2 seconds to complete. Is there something else going on you are not telling us? Are there calculations occuring that are taking additional time? Paul D "trickdos" wrote in message ... Hey all, Thanks for your help. I have a workbook with 2 sheets with 24,000 rows and 10 columns of data, and I need to do a find and replace for all "#Missing" values and return an empty cell. This takes forever. Is there anyway to optimize the macro to run much faster? Application.ScreenUpdating = False With Application Calculation = xlManual End With Sheets("Version 1").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByColumns ActiveWorkbook.PrecisionAsDisplayed = False With Application Calculation = xlAutomatic MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Application.ScreenUpdating = True -- trickdos -------------------------------------------------------------------- ---- trickdos's Profile: http://www.excelforum.com/member.php...fo&userid=9405 View this thread: http://www.excelforum.com/showthread...hreadid=277560 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing a macro for speed- find and replace
That should be the fastest way. Make sure you don't have pagebreaks
visible. -- Regards, Tom Ogilvy "trickdos" wrote in message ... Hey all, Thanks for your help. I have a workbook with 2 sheets with 24,000 rows and 10 columns of data, and I need to do a find and replace for all "#Missing" values and return an empty cell. This takes forever. Is there anyway to optimize the macro to run much faster? Application.ScreenUpdating = False With Application Calculation = xlManual End With Sheets("Version 1").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByColumns ActiveWorkbook.PrecisionAsDisplayed = False With Application Calculation = xlAutomatic MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Application.ScreenUpdating = True -- trickdos ------------------------------------------------------------------------ trickdos's Profile: http://www.excelforum.com/member.php...fo&userid=9405 View this thread: http://www.excelforum.com/showthread...hreadid=277560 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Find & Replace | Excel Worksheet Functions | |||
Macro: Find and replace | Excel Discussion (Misc queries) | |||
optimizing a macro | Excel Programming | |||
find and replace macro need help | Excel Programming | |||
A Macro to Do Find and Replace | Excel Programming |