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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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

V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
128
vancher19
V
L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
339
Linda Lo
L
J
  • Solved
Assistance with excel macro
Replies
3
Views
259
dreid1011
dreid1011
N
  • Question Question
Not understanding loop in vba
Replies
7
Views
316
jolivanes
jolivanes
J
  • Solved
VBA Code Error Unable to get the Match property of the WorksheetFunction class
Replies
8
Views
392
Jiraya_00
J

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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