Find all values within a list and replace with one new value

[astarr57]

I tried searching the forum for this answer, but I did not find what I thought was an exact match so I would like to throw this out to the collective and see what you all recommend. Thanks in advance.

All of my data is contained on one sheet.

I have a range of values that I can easily load into Column A or even paste into a Macro if need be. I would like to find all instances of those values and replace them with a unique value. I was thinking of the word DELETE.

The values in the column A or the data set will be unique five letter codes like ABEBC, etc. There are approximately 3,400 unique codes in this list.

I am not too concerned about the values being in the middle of another longer word elsewhere in the sheet as the five letter codes are fairly specific and not very common. But if I could restrict the replace to the B Column that would be ideal.

Any recommendations would be MUCH appreciate. If any more information is needed from me, please let me know. Thanks.

Astarr57

 

[Cindy Ellis]

Hello astarr57, and Welcome to Mr. Excel!
The following should be tested in a COPY of your workbook, as it replaces original data with the word "DELETE".

To use it, put the list of codes in Column A on a worksheet in the same workbook as the data you're replacing. If that sheet isn't "Sheet2", then change the sheet name in quotes in the code to the sheet name that has the list of codes.

If your main data sheet isn't Sheet1, change that string in the code to match your sheet name.

It should replace any instances of the listed codes with the word DELETE on the main data sheet.

Option Explicit
Sub ReplaceCodes()
'
' CLE 12-29-11
' This macro replaces a list of codes with the word DELETE.
' Put the list in Column A, starting with Row 1, in a worksheet in the same workbook as the data to be replaced

    Dim i As Long, LastDataRow As Long
    Dim Codes As String, Main As String
    Dim CodeArray()
    Dim rng1 As Range
    
    Main = "Sheet1"   'Change quoted text to the name of the worksheet that has all the data
    Codes = "Sheet2"   'Change to the name of the ws that the list of codes to be replaced
    
    LastDataRow = Sheets(Main).Cells(Rows.Count, 2).End(xlUp).Row 'finds the last cell in Column B that has data
    
    With Sheets(Codes)
        If .FilterMode Then .ShowAllData
        Set rng1 = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))  'puts the list into an array to speed things up since you have so many replacements to make
    End With
    
    CodeArray() = rng1.Value
    
    With Worksheets(Main)
        For i = 1 To UBound(CodeArray)
            .Range("B1:B" & LastDataRow).Replace What:=CodeArray(i, 1), Replacement:="DELETE", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next i
    End With

End Sub

I tested this with a short list of dummy codes, and it appears to work.

Best of luck with your project,

 

[astarr57]

Cindy,

Thank you SO much for this. I really appreciate your help. I did get an error that I am not sure how to debug when I ran the macro.

I will walk you through the steps I took and give you the error to see if I did something wrong or if a tweak is necessary.

I opened a copy of the workbook. I went in to create a new macro and pasted exactly what you supplied in your post making sure to avoid duplicating the last end sub line.

I then moved the list of values I want replaced onto Sheet2 in Column A. The data to be replaced is on Sheet1 in Column A.

I ran the macro, and it returned an error of "ShowAllData method of Worksheet class failed."

When I clicked debug this is line where the text it highlighted.

With Sheets(Codes)
If .FilterMode Then .ShowAllData
Set rng1 = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp)) 'puts the list into an array to speed things up since you have so many replacements to make
End With


Again, thank you very much for your assistance. If you have any recommendations, I would be most grateful.

Starr

 

[Cindy Ellis]

It may have something to do with differences in the version of Excel we're running, but I'm not sure. Try commenting out that line and see what happens. It's just there to un-filter the data if you had filtered it for some reason (and should have been added for both sheets, not just the one with the codes). Any data that is hidden (filtered out) when the macro is run won't be replaced.
Also, you mentioned that the data to be replaced is in Column A on sheet 1. The macro is only looking in Column B. If the codes are really in Column A, you can make that adjustment in the code (look for the Range statement in the "With Sheets(main)" block, and change "B" to "A").
Hope that helps,

 

[astarr57]

Since I did not have the data filered, I took out that line. Added in a blank column to push the data into row B, and it appears to have worked.

I could kiss you! Thank you so much. This definitely saved me a days worth of work. Otherwise I would have had to manually delete out those 3,400 values across approximately 7,000 rows of content.

Thank you again. That totally made my day if not entire week.

 

[Cindy Ellis]

...and your feedback made my day
Thanks for letting me know that it worked out OK.