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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
104
vancher19
V
L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
318
Linda Lo
L
J
  • Solved
Assistance with excel macro
Replies
3
Views
249
dreid1011
dreid1011
N
  • Question Question
Not understanding loop in vba
Replies
7
Views
299
jolivanes
jolivanes
J
  • Solved
VBA Code Error Unable to get the Match property of the WorksheetFunction class
Replies
8
Views
293
Jiraya_00
J

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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