Hi,
I have below code that i am using to replace values in a worksheet. It works fine however taking too long to run. There are over 600K rows in the sheet. Is there a way to simplify this code to run faster and more efficiently?
I have below code that i am using to replace values in a worksheet. It works fine however taking too long to run. There are over 600K rows in the sheet. Is there a way to simplify this code to run faster and more efficiently?
VBA Code:
Sub ReplaceName()
'
Application.ScreenUpdating = False
'
Dim lr As Long
Dim cell As Range
'
lr = Worksheets("POS").Range("B" & Rows.Count).End(xlUp).Row
'
For Each cell In Worksheets("POS").Range("B2:B" & lr)
cell.Value = Replace((cell.Value), "Zebra Pen Corporation", "Zebra")
cell.Value = Replace((cell.Value), "Newell Brands", "Newell")
cell.Value = Replace((cell.Value), "Pilot Pen", "Pilot")
Next
For Each cell In Worksheets("POS").Range("G2:G" & lr)
cell.Value = Replace((cell.Value), "Total Brick & Mortar", "Brick & Mortar")
cell.Value = Replace((cell.Value), "Total Ecommerce", "Ecommerce")
Next
For Each cell In Worksheets("POS").Range("E2:E" & lr)
cell.Value = Replace((cell.Value), "Not Applicable", "All Other")
cell.Value = Replace((cell.Value), "Not Specified", "All Other")
Next
For Each cell In Worksheets("POS").Range("H2:H" & lr)
cell.Value = Replace((cell.Value), "$ Velocity - Weighted", "$ Velocity")
cell.Value = Replace((cell.Value), "Unit Velocity - Weighted", "Unit Velocity")
cell.Value = Replace((cell.Value), "% Distribution - Weighted", "% Distribution")
cell.Value = Replace((cell.Value), "% of Stores Selling - Unweighted", "% of Stores Selling")
cell.Value = Replace((cell.Value), "Avg # of Items Where Carried - Weighted", "Avg # of Items")
cell.Value = Replace((cell.Value), "$ Velocity per Items Carried - Weighted", "$ Velocity")
cell.Value = Replace((cell.Value), "Unit Velocity per Items Carried - Weighted", "Unit Velocity")
Next
For Each cell In Worksheets("POS").Range("F2:F" & lr)
cell.Value = Replace((cell.Value), "Single", "1")
Next
Application.ScreenUpdating = True
End Sub