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

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 m开发者_高级运维ake 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.

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).

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

Likely you need to disable calculations (as mentioned) and possibly other things like screen updates as well.

To disable calculations:

If you have Excel 2007 or later (with the Ribbon):

• Go to the Ribbon menu (the circle thing at top left).
• Click "Excel Options" near the bottom right.
• Click the "Formulas" category
• Under calculation options, select "Manual"

Setting calculations to manual will not help, nor will writing a VBA code.

The only thing you need to do is to keep all relevant files open.

For Example:
Say you have 3 files: A, B and C. C collects info from A, but now wants info from B instead.

You update the link with find/replace.

The solution is to keep both B and C open at the same time, then it works flawlessly.

Jon Helgason
Statistics Iceland

Based on excelly cellyson's code, here's a hopefully quite seasoned macro that I made for myself. Works on Excel 2016 (and there's no reason it shouldn't work in previous versions).

Option Explicit

Sub FastReplace(Optional CalculateAfterReplace As Boolean = True)

Dim SelectedRange As Range
Dim What As String, Replacement As String

    'Let's set the 3 input data in place, and allow the user to exit if he hits cancel (or if he wants to look for an emprty string)
    Set SelectedRange = Selection

    What = InputBox("This macro will work on the EXISTING selection, so please cancel and restart it if you haven't selected the desired range." _
        & vbCrLf & vbCrLf & "The selection is " & SelectedRange.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False) _
        & vbCrLf & vbCrLf & "What is the text that needs to be replaced?", "Fast replace stage 1 of 2")
    If What = "" Then Exit Sub

    Replacement = InputBox("You chose to look for " _
    & vbCrLf & vbCrLf & """" & What & """" _
    & vbCrLf & vbCrLf & "Now, what is the replacement text?", "Fast replace stage 2 of 2")
    If StrPtr(Replacement) = 0 Then Exit Sub 'We want to allow an empty string for replacement, hence this StrPtr trick, source https://stackoverflow.com/questions/26264814/how-to-detect-if-user-select-cancel-inputbox-vba-excel

Dim StoreCalculation As Integer

On Error GoTo FastReplace_error 'So that we're not stuck due to the ScreenUpdating = False in case of an error

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

    'Let's log what we're doing in the debug window, just in case
    Debug.Print "Working on " & SelectedRange.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
    Debug.Print "Replacing """ & What & """ for """ & Replacement & """."
    Debug.Print "CalculateAfterReplace = " & CalculateAfterReplace

    'The heart of this sub
    SelectedRange.Replace What:=What, Replacement:=Replacement, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    'Wrapping up
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = StoreCalculation
    If CalculateAfterReplace Then Application.CalculateFull
    Beep
    Exit Sub

FastReplace_error:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = StoreCalculation
    If CalculateAfterReplace Then Application.CalculateFull
    Err.Raise Err.Number, Err.Source, Err.Description

End Sub