Excel Auto-Replace Macro

Question

In case this question sounds too basic, I'm telling that I have zero experience on Excel VBA. I've already tried using Dim and arrays but I'm not achieving my goal. I either get errors or an infinite loop.

Basically I have 5 different old text to replace and I want to create an array, loop around it so that they are all removed.

Sub AutoReplace()

Dim myArray(1 To 5) As String
myArray(1) = "Text1"
myArray(2) = "Text2"
myArray(3) = "Text3"
myArray(4) = "Text4"
myArray(5) = "Text5"

For Each item In myArray
ActiveCell.Select
ActiveCell.Columns("A:A").EntireColumn.Select

    Selection.Replace What:=item, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next item

End Sub

I'm looking for either a suggestion on what's wrong with my code, or a totally different approach. The end goal I have mentioned above only.

Why are you using `ActiveCell` and select it each time? And besides, if you "get errors" you should also say what the errors are and on which line of code. — Voitcus, May 09 '13 at 06:58
See, I already told I'm a complete noob, so I may not entirely know the explanation to all things I'm trying. My issue is not about solving a specific error on a line of code, it's about achieving the function's goal. — Question Everything, May 09 '13 at 08:01

score 2 · Accepted Answer · edited May 23 '17 at 12:13

Try below code. Avoid using Select/Activate. To know why click on this link. Also you can explicitly specify the sheet name like Sheets("sheet1").Columns("A:A") else it will take ActiveSheet

Sub AutoReplace()

    Dim myArray(1 To 5) As String
    myArray(1) = "Text1"
    myArray(2) = "Text2"
    myArray(3) = "Text3"
    myArray(4) = "Text4"
    myArray(5) = "Text5"

    For Each Item In myArray

        Columns("A:A").Replace What:=Item, Replacement:="", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                          ReplaceFormat:=False
    Next Item

End Sub

Excel Auto-Replace Macro

1 Answers1