Hi,
I have a worksheet ("POS") where I want to change column A values to "All Other" if Column B values are not "BIC", "Newell Brands", "Crayola", Pilot Pen, "Pentel", "Private Label" and "Zebra Pen Corporation" starting row 2. Below code works however taking forever to run. Appreciate any help to simplify the code to run more efficiently and fast. Thanks
I have a worksheet ("POS") where I want to change column A values to "All Other" if Column B values are not "BIC", "Newell Brands", "Crayola", Pilot Pen, "Pentel", "Private Label" and "Zebra Pen Corporation" starting row 2. Below code works however taking forever to run. Appreciate any help to simplify the code to run more efficiently and fast. Thanks
VBA Code:
Sub ReplaceAllOther()
Application.ScreenUpdating = False
Worksheets("POS").Activate
Dim i As Long
Dim lr As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
If Range("B" & i).Value <> "BIC" And Range("B" & i).Value <> "Newell Brands" And Range("B" & i).Value <> "Crayola" And Range("B" & i).Value <> "Private Label" And Range("B" & i).Value <> "Pilot Pen" And _
Range("B" & i).Value <> "Pentel" And Range("B" & i).Value <> "Zebra Pen Corporation" Then Range("B" & i).Value = "All Other"
If Range("B" & i).Value <> "BIC" And Range("B" & i).Value <> "Newell Brands" And Range("B" & i).Value <> "Crayola" And Range("B" & i).Value <> "Private Label" And Range("B" & i).Value <> "Pilot Pen" And _
Range("B" & i).Value <> "Pentel" And Range("B" & i).Value <> "Zebra Pen Corporation" Then Range("A" & i).Value = "All Other"
Next i
Application.ScreenUpdating = True
End Sub