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.

Can you please show some sample data of what you're talking about? What do you have in these cells and to what are you trying to change them? — Jay, Aug 27 '10 at 15:02
I don't know Excel well enough but isn't there an option to disable immediate re-calculations? — Lieven Keersmaekers, Aug 27 '10 at 15:05
Difficult to show the data (quite sensitive). It's 1610 rows deep and 10 columns long. The cells reference calculations that have been performed on the source category files and how in % format. Sorry I can't help any more. I'm off to try the disable calculations trick. — RocketGoal, Aug 27 '10 at 15:36
Do you have to do this in Excel? What if you store the data in Access, do your calculations there, and then export it to Excel? — PowerUser, Aug 27 '10 at 19:31
I'm doing this for a colleague who has already created the masses of country data Excel, and I'm now responsible for producing the final Excel graphs. So, Access isn'treally a possibility for me - but it's something I want to move my colleague towards. — RocketGoal, Aug 30 '10 at 07:57

score 8 · Accepted Answer · answered Aug 27 '10 at 15:45

8

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

answered Aug 27 '10 at 15:45

Joel Goodwin

5,026
27
30

Just about to post the same. Chances are it is the redrawing of the charts, rather than the recalculation, that is slowing things down so much. – Lunatik Aug 27 '10 at 15:47
As a VBA novice are you creating a macro and putting the first code block in it? And then a second macro with the second code block? USe the first, update then use the second? – RocketGoal Aug 27 '10 at 15:51
Error handling would be : On Error GoTo... And then errHandler would pop up a message allowing me to cancel the procedure? – RocketGoal Aug 27 '10 at 15:55
I would probably have separate subroutines called SwitchOff and SwitchOn which are called by your Find/Replace macro. (Advanced Masterclass: What I actually do is have a special EventSuppressor class that has a startsuppress/killsuppress function that does all this; the Class_Terminate also calls the killsuppress routine in case an error has tripped and skipped the bit that in your macro that calls killsuppress when you finish. This isn't necessary, it's just the way I industrialise this process as I need to use it multiple times.) – Joel Goodwin Aug 27 '10 at 16:00
My current find/replace macro is my right index finger and my sense of direction with the mouse;) But I'll work on my macro skills if it will help with this task. – RocketGoal Aug 27 '10 at 16:05
Aha. Although you could conceivably call these routines from the Macro button before and after you do the Find/Replace, it would be better to create a one-stop shop that does the whole shebang. Consider it an education =) – Joel Goodwin Aug 27 '10 at 16:08
One last point. As suggested by others, you could just try turning off the calculations through the Options->Calculation dialog, make them manual, do your Find/Replace, then turn them back on. To force the entire sheet to recalculate once the update is completed, hit CTRL+ALT+F9 for a mega-recalculate. – Joel Goodwin Aug 27 '10 at 16:11
Mike, missed your comment about "On Error GoTo". You should pop the error to screen so you can see what actually went wrong, but when it's done, it should call the routine that re-enables everything again and exits gracefully. – Joel Goodwin Aug 27 '10 at 16:16

score 5 · Answer 2 · edited Apr 04 '12 at 20:50

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

score 1 · Answer 3 · edited Feb 17 '14 at 21:02

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

score 0 · Answer 4 · answered Aug 27 '10 at 15:17

0

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

answered Aug 27 '10 at 15:17

Anthony -GISCOE-

618
5
12

score 0 · Answer 5 · answered Aug 27 '10 at 15:25

0

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"

answered Aug 27 '10 at 15:25

JYelton

35,664
27
132
191

With disabling calculations, would that mean Excel wouldn't update the graphs until I re-enabled the calculations again? – RocketGoal Aug 27 '10 at 15:33
That's correct; though you can force calculations by pressing F9 or clicking "Calculate Now" (on the Formulas bar). Incidentally the Calculation Options are also on the Formulas bar, which I hadn't realized. – JYelton Aug 27 '10 at 15:39

mll · Answer 6 · 2019-06-28T16:30:08.367

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

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

6 Answers6