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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
263
lneidorf
L
P
  • Solved
Understanding VBA Macro
Replies
5
Views
332
RoryA
RoryA
R
  • Solved
Excel VBA error when attempting to replace text that has multiple lines a cell
2
Replies
11
Views
442
Revco
R
R
  • Question
Replace links after moving worksheets from one workbook to another.
Replies
2
Views
183
RockandGrohl
R
T
  • Solved
I want short code some how
Replies
4
Views
444
mohadin
M

Forum statistics

Threads
1,216,095
Messages
6,128,792
Members
449,468
Latest member
AGreen17

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