Code to do a simple clean of one column by copying and pasting(finding and replacing) phrases in adj

[fjs20000]

Is there some code that would accomplish the following:

In column A I have a large list (i.e., 50,000+ rows) of alphanumeric text that I need to clean. In column B, I have a series of phrases that I want touse to do a Find and Replace with a Blank for each of the cells in column A. So, for example, in cell B2, I have the phrase "used parts". That is a phrase that I want to remove from all cells in Column A via the Find and Replace function (i.e., I want to find "used parts" and replace it with nothing in all cells in column A). Then, I want to move down to cell B3, then B4,etc and keep repeating the process of cleaning column A with the individual phrases in column B.

Ideally, I can reuse the file to copy and paste large portions of data in column A, and also change the phrases (and numbers of phrases) in column B.

I tried to use the Record Macro option, but the macro was too large.

Thanks for any suggestions, or available code that would accomplish this!

 

[fjs20000]

And I am using Excel 2010, if that helps

 

[Claus Busch]

Hi,

Am Wed, 28 May 2014 09:55:47 -0700 (PDT) schrieb (e-mail address removed):

In column A I have a large list (i.e., 50,000+ rows) of alphanumeric text that I need to clean. In column B, I have a series of phrases that I want to use to do a Find and Replace with a Blank for each of the cells in column A. So, for example, in cell B2, I have the phrase "used parts". That is a phrase that I want to remove from all cells in Column A via the Find and Replace function (i.e., I want to find "used parts" and replace it with nothing in all cells in column A). Then, I want to move down to cell B3, then B4, etc and keep repeating the process of cleaning column A with the individual phrases in column B.

try:

Sub FindAndReplace()
Dim LRowA As Long, LRowB As Long
Dim myArr As Variant
Dim i As Long

With ActiveSheet
LRowA = .Cells(Rows.Count, 1).End(xlUp).Row
LRowB = .Cells(Rows.Count, 2).End(xlUp).Row

myArr = .Range("B1:B" & LRowB)
For i = LBound(myArr) To UBound(myArr)
.Range("A1:A" & LRowA).Replace what:=myArr(i, 1), _
replacement:="", lookat:=xlWhole
Next
End With
End Sub


Regards
Claus B.

 

[joeu2004]

In column A I have a large list (i.e., 50,000+ rows) of
alphanumeric text that I need to clean. In column B, I
have a series of phrases that I want to use to do a
Find and Replace with a Blank for each of the cells in
column A. So, for example, in cell B2, I have the phrase
"used parts". That is a phrase that I want to remove from
all cells in Column A via the Find and Replace function
(i.e., I want to find "used parts" and replace it with
nothing in all cells in column A). [....]
Ideally, I can reuse the file to copy and paste large
portions of data in column A, and also change the phrases
(and numbers of phrases) in column B.

Ostensibly, something like this:

Sub doit()
Dim data As Range, phrases As Range, key As Range
Set data = Range("a2:a50000")
Set phrases = Range("b2:b10")
For Each key In phrases
data.Replace What:=key, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next
End Sub

If you want to apply it to all data in column A starting with A2 using all
phrases in column B starting with B2, change the Set statements as follows:

Set data = Range("a2",Cells(Rows.Count,"a").End(xlUp))
Set phrases = Range("b2",Cells(Rows.Count,"b").End(xlUp))

Alternatively, if you want to manually select the range in column A first:

Set data = Selection

 

[fjs20000]

Perfect. Works great! Thanks so much.

In column A I have a large list (i.e., 50,000+ rows) of

alphanumeric text that I need to clean. In column B, I

have a series of phrases that I want to use to do a

Find and Replace with a Blank for each of the cells in

column A. So, for example, in cell B2, I have the phrase

"used parts". That is a phrase that I want to remove from

all cells in Column A via the Find and Replace function

(i.e., I want to find "used parts" and replace it with

nothing in all cells in column A).
[....]

Ideally, I can reuse the file to copy and paste large

portions of data in column A, and also change the phrases

(and numbers of phrases) in column B.

Ostensibly, something like this:



Sub doit()

Dim data As Range, phrases As Range, key As Range

Set data = Range("a2:a50000")

Set phrases = Range("b2:b10")

For Each key In phrases

data.Replace What:=key, Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Next

End Sub



If you want to apply it to all data in column A starting with A2 using all

phrases in column B starting with B2, change the Set statements as follows:



Set data = Range("a2",Cells(Rows.Count,"a").End(xlUp))

Set phrases = Range("b2",Cells(Rows.Count,"b").End(xlUp))



Alternatively, if you want to manually select the range in column A first:



Set data = Selection