VBA code to replace values in a column

[Dokat]

Hi,

I have a data table with over 300K rows. In worksheet ("POS")column B i have values like "Zebra Tire" that i'd like to replace with "Zebra" or "Sumo Tires" with "Sumo". I tested below code however it didn't do anything. How can i replace the values in Column B with vba?

Sub ReplaceName()

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

Range("B" & lr).Value = Replace(Range("B" & lr), "Zebra Tire", "Zebra")

    Next i
Application.ScreenUpdating = True
End Sub

 

[Dokat]

Hi,

I have a data table with over 300K rows. In worksheet ("POS")column B i have values like "Zebra Tire" that i'd like to replace with "Zebra" or "Sumo Tires" with "Sumo". I tested below code however it didn't do anything. How can i replace the values in Column B with vba?

Sub ReplaceName()

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

Range("B" & lr).Value = Replace(Range("B" & lr), "Zebra Tire", "Zebra")

    Next i
Application.ScreenUpdating = True
End Sub

 

[Dokat]

Below code worked for me

Sub ReplaceName()

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

Worksheets("POS").Range("B" & i).Value = Replace(Worksheets("POS").Range("B" & i), "Zebra Tires", "Zebra")

    Next i
Application.ScreenUpdating = True
End Sub

 

[kokano90]

Below code worked for me

Can't you use the Replace function?

 

[Dokat]

Can't you use the Replace function?

I did. Above code worked

 

[johnnyL]

Sub Test()
'
    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("B1:B" & lr)
        cell.Value = Replace((cell.Value), "Zebra Tire", "Zebra")
    Next
'
    Application.ScreenUpdating = True
End Sub

 

[Dokat]

Sub Test()
'
    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("B1:B" & lr)
        cell.Value = Replace((cell.Value), "Zebra Tire", "Zebra")
    Next
'
    Application.ScreenUpdating = True
End Sub

Thanks for your response. I tried the code but didnt work. It didnt replace any value. Second code i posted actually worked. Only challenge is it takes too long to run

 

[johnnyL]

The code I posted works. I wouldn't have posted it otherwise.

 

[Dokat]

The code I posted works. I wouldn't have posted it otherwise.

You are right. I retried it and it worked. Not sure what happened the first time but it is running very efficiently. Thanks

 

[Peter_SSs]

Can't you use the Replace function?

I did. Above code worked

I believe that what @kokano90 meant was this, doing all 300K rows at once rather than one at a time.

Sub Test2()
  Worksheets("POS").Columns("B").Replace What:="Zebra Tire", Replacement:="Zebra", LookAt:=xlPart, MatchCase:=False
End Sub