2
\$\begingroup\$

I have a working macro that finds a specific word and deletes the whole text. It finds the word "JOBSPECIALTYCODE" in the sheet, selects and deletes it, then moves to the next sheet. I just want to ask if there is any alternative way to make my macro work fast. I only use a record macro.

enter image description here

Sub aHaveProgess()
'
'
'
'

'Deleting Trash Text to make it clear

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
    .Text = "JOBSPECIALTYCODE"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchByte = False
    .CorrectHangulEndings = False
    .HanjaPhoneticHangul = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = False
    .MatchFuzzy = False
End With

Do While Selection.Find.Execute
    Selection.SelectCell
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
Loop
End Sub
\$\endgroup\$
2
\$\begingroup\$

ScreenUpdating

In order for you to interact with a spreadsheet (e.g. moving the mouse, selecting a cell) , the display on your monitor is re-drawn something like 30+ times per second. This takes a lot of processing power.

If you set Application.ScreenUpdating = False then, while it is false, the screen will not be updated, and your code will run much faster.

Crucially though, make sure it is set back to True before your code ends. Else your code will finish running, but your user will think that Excel has frozen because the screen won't respond to their mouse.

Make sure that before any End/Exit statements, or if errors are thrown, that you change the setting back.

Like so:

Public Sub DoThing()

    Application.ScreenUpdating = False

    On Error Goto CleanFail

    ...

    ...

    Code

    ...

    ...

CleanExit:

    Application.ScreenUpdating = True

    Exit Sub

CleanFail:

    On Error Goto 0

    '/ Handle errors here

    Resume CleanExit

End Sub

And now, assuming you put some kind of error handling in, your sub will always exit through CleanExit, which will always reset ScreenUpdating back to normal.

\$\endgroup\$
  • 1
    \$\begingroup\$ Worth noting that setting ScreenUpdating back to true is not explicitly required. Excel does it automatically when the procedure ends. \$\endgroup\$ – Scott Holtzman Jun 30 '16 at 21:15
  • 2
    \$\begingroup\$ @ScottHoltzman True, but exactly the kind of non-intuitive not-explicitly-documented behaviour that should be avoided in any and all code. Also not true for the other application settings, so a good habit to get into. \$\endgroup\$ – Kaz Jun 30 '16 at 21:26
  • 1
    \$\begingroup\$ appreciate that POV @Zak. \$\endgroup\$ – Scott Holtzman Jun 30 '16 at 21:32
  • 1
    \$\begingroup\$ @Zak Thank you Zak it's work fine. it's take only 1minute i think rather before its about 3-4mins and i dont know if it's still running. Thank again and very much appreciate. \$\endgroup\$ – P.Alvs Jul 1 '16 at 2:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.