[RESOLVED] Remove specific from 2 columns (VBA)

[Radjesh Klauke]

Hi,

I have to remove/replace a couple of specific characters ( "-", "(",")","/" ) from Columns "O" & "X" (UsedRange). Anyone knows how to code this? I know I can use "Replace What:=", but how to with multiple characters? Thanks in advance.

[vbfbryce]

There's probably a better way than this, especially if you have more than a few characters to replace. This example replaces 3 characters (stored in variables), found in column A, with replacement text.

Sub replaceThem()
    Dim rep1 As String
    Dim rep2 As String
    Dim rep3 As String
    Dim repWith As String
    
    rep1 = ","
    rep2 = "("
    rep3 = ")"
    repWith = "abc"
    
    Range("a1").EntireColumn.Replace what:=rep1, replacement:=repWith, lookat:=xlPart, _
        searchorder:=xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
    Range("a1").EntireColumn.Replace what:=rep2, replacement:=repWith, lookat:=xlPart, _
        searchorder:=xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
    Range("a1").EntireColumn.Replace what:=rep3, replacement:=repWith, lookat:=xlPart, _
        searchorder:=xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
End Sub

[Radjesh Klauke]

It's better then nothing m8. If someone has a better solution, i'd love to hear it.

[vbfbryce]

One thing you could do is create an array of characters to be replaced, then loop through the "replace" code, rather than repeating it.

[Radjesh Klauke]

Do you have an example on how to do that?
Thanks in advance.

[vbfbryce]

Sub replaceThem()
    Dim rep1 As String
    Dim rep2 As String
    Dim rep3 As String
    Dim repWith As String
    Dim replChars(3) As String
    Dim i As Integer
    Dim rng As Range
    
    replChars(0) = ","
    replChars(1) = "("
    replChars(2) = ")"
    replChars(3) = "-"
    repWith = "abc"
    Set rng = Range("a1").EntireColumn
    
    For i = 0 To 3
        rng.Replace what:=replChars(i), replacement:=repWith, lookat:=xlPart, _
        searchorder:=xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False
    Next i
    
End Sub