VBA: How do I stop this code from doing all of the screen flicking?

S

SteveOranjin

Board Regular
Joined
Dec 18, 2017
Messages
170
I recorded this macro a little while ago, and I'm hoping someone can help me clean it up a little bit. What this macro does when it starts is it does all the screen flicking that hit the code when I was recording it. I'm getting a little better with VBA, and have been making my additions to the code directly. Can someone tell me what the function is so I can have this VBA do all the removing without having to sit there and run through all the screen flicking when it operates?

Hope you are well,

Steve

Code: 
ub Find_And_Replace()'·
' Find_And_Replace Macro
'


'
    Sheets("Sheet1").Select
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Cells.Select
    Selection.replace What:="$", Replacement:="", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="""", Replacement:="''", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="®", Replacement:="", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="·", Replacement:="", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="”", Replacement:="", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="”", Replacement:="", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="&", Replacement:="and", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="½", Replacement:="1/2", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="¼", Replacement:="1/4", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="¾", Replacement:="3/4", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="°", Replacement:=" degrees", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False ' note the space in " degrees"
    Selection.replace What:="™", Replacement:="", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="", Replacement:="", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ' THESE ARE ONLY REMOVED WHEN THEY ARE THE SOLE CONTENT OF THE ENTIRE CELL
    Selection.replace What:="N/A", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="N/A ", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="#N/A", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="N", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="n", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="n/a", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False ' "n/a" without a space
    Selection.replace What:="n/a ", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False ' "n/a" with a space
    Selection.replace What:="#n/a", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.replace What:="y", Replacement:="Yes", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False ' "y" without a space
    Selection.replace What:="y ", Replacement:="Yes", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False ' "y" with a space
    Selection.replace What:="No", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False ' "No" without a space
    Selection.replace What:="No ", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False ' "No" with a space
    Selection.replace What:="#VALUE!", Replacement:="", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False


    Sheets("Sheet2").Select
    Range("A1").Activate
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Perhaps Change
Code: 
    Cells.Select
    Selection.replace

by

Code: 
    Cells.replace</pre>
 
Upvote 0
Put this at the beginning of your code...
Code: 
Application.ScreenUpdating = False
and put this at the end of your code...
Code: 
Application.ScreenUpdating = True
 
Upvote 0
Along with turning screenupdating off, you can also use a with statement
Code: 
Sub Find_And_Replace() '·
' Find_And_Replace Macro
'


Application.ScreenUpdating = False
   With Sheets("Sheet1").UsedRange
      .Replace What:="$", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="""", Replacement:="''", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="®", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="·", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="”", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="”", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="&", Replacement:="and", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="½", Replacement:="1/2", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="¼", Replacement:="1/4", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="¾", Replacement:="3/4", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="°", Replacement:=" degrees", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' note the space in " degrees"
      .Replace What:="™", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      ' THESE ARE ONLY REMOVED WHEN THEY ARE THE SOLE CONTENT OF THE ENTIRE CELL
      .Replace What:="N/A", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="N/A ", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="#N/A", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="N", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="n", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="n/a", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "n/a" without a space
      .Replace What:="n/a ", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "n/a" with a space
      .Replace What:="#n/a", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="y", Replacement:="Yes", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "y" without a space
      .Replace What:="y ", Replacement:="Yes", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "y" with a space
      .Replace What:="No", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "No" without a space
      .Replace What:="No ", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "No" with a space
      .Replace What:="#VALUE!", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
   End With
   Sheets("Sheet2").Select
   Range("A1").Activate
End Sub
 
Last edited:
Upvote 0
Fluff said:
Along with turning screenupdating off, you can also use a with statement
Code: 
Sub Find_And_Replace() '·
' Find_And_Replace Macro
'


Application.ScreenUpdating = False
   With Sheets("Sheet1").UsedRange
      .Replace What:="$", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="""", Replacement:="''", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="®", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="·", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="”", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="”", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="&", Replacement:="and", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="½", Replacement:="1/2", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="¼", Replacement:="1/4", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="¾", Replacement:="3/4", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="°", Replacement:=" degrees", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' note the space in " degrees"
      .Replace What:="™", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="", Replacement:="", Lookat:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      ' THESE ARE ONLY REMOVED WHEN THEY ARE THE SOLE CONTENT OF THE ENTIRE CELL
      .Replace What:="N/A", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="N/A ", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="#N/A", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="N", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="n", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="n/a", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "n/a" without a space
      .Replace What:="n/a ", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "n/a" with a space
      .Replace What:="#n/a", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
      .Replace What:="y", Replacement:="Yes", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "y" without a space
      .Replace What:="y ", Replacement:="Yes", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "y" with a space
      .Replace What:="No", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "No" without a space
      .Replace What:="No ", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False ' "No" with a space
      .Replace What:="#VALUE!", Replacement:="", Lookat:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
   End With
   Sheets("Sheet2").Select
   Range("A1").Activate
End Sub
Click to expand...

Thank you!

Steve
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Clean Text - Remove Repeat Line Breaks
Replies
4
Views
212
lneidorf
L
P
  • Solved
Understanding VBA Macro
Replies
5
Views
319
RoryA
RoryA
R
  • Solved
Excel VBA error when attempting to replace text that has multiple lines a cell
2
Replies
11
Views
405
Revco
R
R
  • Question
Replace links after moving worksheets from one workbook to another.
Replies
2
Views
134
RockandGrohl
R
T
  • Solved
I want short code some how
Replies
4
Views
431
mohadin
M

Forum statistics

Threads
1,215,464
Messages
6,124,966
Members
449,200
Latest member
Jamil ahmed

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top