Dears,
I have a thousand of data rows, which i needed to rename and remove a specific string from sheet2 if found in columnA data string in sheet1.
Please see sample file.
tnxs..
ivan
Dears,
I have a thousand of data rows, which i needed to rename and remove a specific string from sheet2 if found in columnA data string in sheet1.
Please see sample file.
tnxs..
ivan
Re: Excel VBA - Search and Remove String Data
Try:-
Sub Rep()
Dim Ray As Variant, Ray2 As Variant, n As Long
Ray = Sheets("Sheet1").Range("A1").CurrentRegion.Resize(, 1)
Ray2 = Sheets("Sheet2").Range("A2").CurrentRegion.Resize(, 1)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For n = 1 To UBound(Ray2, 1)
.Item(Ray2(n, 1)) = Empty
Next
For n = 2 To UBound(Ray, 1)
If .exists(Trim(Split(Ray(n, 1), "-")(1))) Then
Ray(n, 1) = Trim(Split(Ray(n, 1), "-")(0))
End If
Next n
End With
Sheets("Sheet1").Range("A1").Resize(UBound(Ray, 1)) = Ray
End Sub
Display More
Re: Excel VBA - Search and Remove String Data
Wow.. It's work perfect. Thanks a lot MickG.. :wink:
Re: Excel VBA - Search and Remove String Data
You're welcome
Don’t have an account yet? Register yourself now and be a part of our community!