need Help - in VBA code

[Mahantesh]

Hi Team,

I need help in VBA Code which deletes the garbage letters from each Excel rows.

Eg: i have attached a workbook. When you apply filter col-E, you will see something like
xxxxxxxxxxxxxxxxxxx,sssssssssssssssssssssss, aaaaaaaaaaaaaaaaaaaaaa etc.

I want to remove these values and replace them with Blank as new cell value.

Can anyone please help me.

thank you

Regards,
Mahantesh

 

[shrivallabha]

Something like below shall help your situation.

Public Sub RemoveRepeatChars()
Dim rgEx As Object 'regExp
Dim rng As Range
Set rgEx = CreateObject("VBScript.RegExp")
With rgEx
    .MultiLine = False
    .IgnoreCase = True
    '\\Replace "\w" with "." without quotes if you have non alpha repeats
    .Pattern = "^(\w)\1+$"
End With

Application.ScreenUpdating = False
For Each rng In Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row)
    If rgEx.Test(rng.Value) = True Then
        rng.Value = ""
    End If
Next rng
Application.ScreenUpdating = True

Set rgEx = Nothing
End Sub

 

[Mahantesh]

Hi Guru,

This was an ultimate solution. It solved my many problem. Thanks a ton mate!

regards,
Mahantesh