Excel VBA Find and replace within range

I need to replace words only in columns D through U. My code keeps replacing parts of cells in coumns A-C as well as D-U. This code below takes a long time to run because it loops through each line but I'm not sure how to do this. How can I avoid making changes in columns A-C and speed up the find and replace?

Application.ScreenUpdating = False

Dim LR As Long
Dim i As Long
 
    LR = Range("D" & Rows.Count).End(xlUp).Row
    
     For i = 3 To LR
        Cells.Replace What:="sod", Replacement:="0", LookAt:=xlPart, SearchOrder _
           :=xlByRows, MatchCase:=False
        Cells.Replace What:="sdo", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
        Cells.Replace What:="sick", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
        Cells.Replace What:="dso", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
        Cells.Replace What:="off", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
        Cells.Replace What:="all", Replacement:="0", LookAt:=xlPart, SearchOrder _
           :=xlByRows, MatchCase:=False
        Cells.Replace What:="week", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
        Cells.Replace What:="quit", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
        Next i
Application.ScreenUpdating = True

End Sub

Open in new window

minamina6Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

Or maybe better, instead of Range("D:U"), you could use Range("D3:U" & Range("D" & Rows.Count).End(xlUp).Row) to exclude the first lines and drop your loop, which is, by now, unnecessary.

Regards
0
 
Rgonzo1971Commented:
Hi,

You should specify the range instead of cells (the whole worksheet), it could be Columns("D:U")

Regards
0
 
Wayne Taylor (webtubbs)Connect With a Mentor Commented:
Try this instead...

Application.ScreenUpdating = False

Dim LR As Long
Dim i As Long
 
    LR = Range("D" & Rows.Count).End(xlUp).Row
    
     For i = 3 To LR
        With Range("D:U")
               .Replace What:="sod", Replacement:="0", LookAt:=xlPart, SearchOrder _
           :=xlByRows, MatchCase:=False
               .Replace What:="sdo", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
             .Replace What:="sick", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
              .Replace What:="dso", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
              .Replace What:="off", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
              .Replace What:="all", Replacement:="0", LookAt:=xlPart, SearchOrder _
           :=xlByRows, MatchCase:=False
              .Replace What:="week", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
              .Replace What:="quit", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
        End With
        Next i
Application.ScreenUpdating = True

End Sub

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
minamina6Author Commented:
This still makes changes in columns A-C. I took out some of the replacement code to make this more readable.

With Range("D3:U" & Range("D" & Rows.Count).End(xlUp).Row)
        Cells.Replace What:="all", Replacement:="0", LookAt:=xlPart, SearchOrder _
           :=xlByRows, MatchCase:=False
        Cells.Replace What:="sdo", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
        Cells.Replace What:="sick", Replacement:="0", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False
         End With

Open in new window

0
 
Rgonzo1971Commented:
HI,

You have to delete  all the "Cells" but the dot must remain.

Regards
0
 
minamina6Author Commented:
You mean like this
.Replace What:="all", Replacement:="0", LookAt:=xlPart, SearchOrder _
           :=xlByRows, MatchCase:=False

Open in new window


instead of this?
Cells.Replace What:="all", Replacement:="0", LookAt:=xlPart, SearchOrder _
           :=xlByRows, MatchCase:=False

Open in new window

0
 
Rgonzo1971Commented:
Hi,

Yes, the with instruction allows this

Regards
0
 
minamina6Author Commented:
That seemed to work. Thanks!
0
 
Rgonzo1971Commented:
All that jazz for 50 points :-(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.