Excel: How can I speed up the search and replace process? Change 32,000 book links

Question

I compare a lot of data for more than 30 categories. Each category book is saved in "MyFolder" with its own distinguished name (category). The data in the workbook is on a worksheet with the same name as the category: [Category.xls] CategoryYear_Final!

It seems best to create a standard template that references the data and which creates the necessary graphics and layouts. Everything worked well. It's time to start the engines and make graphics for all categories by changing the names of the links ...

Using FIND and REPLACE, it takes more than 20 minutes of each workbook, as there are more than 32,000 places (two per cell) where updates should take place. Crikey!

Any suggestions on how this can be done faster, or I just need to catch a lasting 20 hours of viewing Excel.

Thanks a lot Michael.

Answer 1

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

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

After the update is completed:

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

You might want to make sure that if you have problems with your update, you will catch an error and in any case follow the re-enable procedure. For example, if you make a mistake and cannot re-enable Excel ScreenUpdating, this will make the session unusable by the user (although it can be fixed through the nearest VBA editor window if you know what to do).

Answer 2

Excel 2010. -! 851000 10 .

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

Answer 3

, ( ) , , , .

Answer 4

:

Excel 2007 ( ):

• ( ).
• " Excel" .
• ""
• ""

Answer 5

VBA.

, , .

:
, 3 : A, B C. C A, B.

find/replace.

, B, C, .

Answer 6

cellyson, , , , . Excel 2016 ( , ).

Option Explicit

Sub FastReplace(Optional CalculateAfterReplace As Boolean = True)

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

    'Let 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 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