Excel: How can I speed up a Find & Replace process? Changing 32,000 links per workbook

Question

I'm comparing a lot of data for over 30 categories. Each category workbook is saved into 'MyFolder' with it's own distinctive name (the category). The data in the workbook is found on a sheet with the same name as the category: [Category.xls]CategoryYear_Final!

It seemed best to create a standard template that references the data and which produced the required graphs and layouts. It all worked well. The it was time to start the engines and make graphs for all the categories by amending the reference names...

Using FIND & REPLACE it's taking over 20 mins each workbook as there are over 32,000 places (two per cell) where the updates must take occur. Crikey!

Any sugestions on how this could possibly be done more quickly, or do I just need to settle in for a solid 20 hours of watching Excel struggle through.

Many thanks Michael.

Answer 1

This is what I would do. Before doing the update:

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

After doing the update:

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.CalculateFull

You might want to make sure that if there's a problem with your update, you catch the error and go through the re-enable routine anyway. For example, if you error and fail to re-enable Excel ScreenUpdating, it makes the session unusable to a user (although it can be fixed through the VBA editor immediate window if you know what to do).

Answer 2

Works in Excel 2010. This is super-fast! Made 851000 replacements in approximately 10 seconds.

Sub Macro1()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' fill your range in here
    Range("E3:CN9254").Select
    ' choose what to search for and what to replace with here
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.CalculateFull

End Sub