Could someone possibly send me the code that could accomplish the following in Sheet1?

1. Column A, titled "Phrase" contains 500 or more alphanumeric Strings
2. Column B, titled "Find and Delete" contains a single word or phrase (i.e., different phrases throughout the column)


The macro would go row by row, essentially Finding the values that are in Column B and, if it finds a match, it would delete that word or phrase from Column A.

In the example below, the values "Apple" and "Big" would be left remaining in column A, after the macro runs.

(A1)Phrase (B1)Find and Delete
(A2)Apple Jack (B2) Jack
(A3)Big Bug (B3) Bug

Thanks very much for any assistance.

Hi James,

Am Wed, 30 Dec 2015 16:09:56 -0800 (PST) schrieb James:

(A1)Phrase (B1)Find and Delete
(A2)Apple Jack (B2) Jack
(A3)Big Bug (B3) Bug

try:

Sub Replace()
Dim LRow As Long
Dim rngC As Range

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A2:A" & LRow)
rngC.Replace what:=rngC.Offset(, 1), replacement:=""
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi james,

Am Thu, 31 Dec 2015 11:07:29 +0100 schrieb Claus Busch:

(A1)Phrase (B1)Find and Delete
(A2)Apple Jack (B2) Jack
(A3)Big Bug (B3) Bug

try:

Sub Replace()

sorry, I first misunderstood your problem.
Please try:

Sub Replace()
Dim LRow As Long, i As Long
Dim myRng As Range
Dim varRep As Variant

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set myRng = .Range("A2:A" & LRow)
LRow = .Cells(Rows.Count, 2).End(xlUp).Row
varRep = .Range("B2:B" & LRow)

For i = LBound(varRep) To UBound(varRep)
myRng.Replace varRep(i, 1), ""
Next
End With
End Sub



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Find the code

Sub Replace_Words()

Dim LastrowSht1 As Long
Dim LastrowSht2 As Long
Dim Workrng As Range

LastrowSht1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LastrowSht2 = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row

Set Workrng = Sheets("Sheet1").Range(Cells(1, 1), Cells(LastrowSht1, 1))
Debug.Print Workrng.Address

For nrow = 2 To LastrowSht2
Workrng.AutoFilter field:=1, Criteria1:="*" & Sheets("Sheet1").Cells(nrow, 2) & "*"
Workrng.SpecialCells(xlCellTypeVisible).Replace What:=Sheets("Sheet1").Cells(nrow, 2), replacement:=""
ActiveSheet.AutoFilterMode = False
Next

End Sub

"James" wrote:

1. Column A, titled "Phrase" contains 500 or more alphanumeric Strings
2. Column B, titled "Find and Delete" contains a single word or phrase
(i.e., different phrases throughout the column)

The macro would go row by row, essentially Finding the values that are in
Column B and, if it finds a match, it would delete that word or phrase
from Column A.

In the example below, the values "Apple" and "Big" would be left remaining
in column A, after the macro runs.

(A1)Phrase (B1)Find and Delete
(A2)Apple Jack (B2) Jack
(A3)Big Bug (B3) Bug


Here is my solution:

'
' Remember to click on Tools-References.
' Then select Microsoft VBScript Regular Expressions 5.5
'
Sub TryThis()

Dim ptrn As String
Dim re As Object
Dim i As Integer

Set re = New RegExp

For i = 1 To 500
re.Pattern = Sheet1.Cells(i + 1, 2)
re.IgnoreCase = True
re.Global = True
Sheet1.Cells(i + 1, 1) = re.Replace(Sheet1.Cells(i + 1, 1), "")
Next

End Sub

Here is my solution:

'
' Remember to click on Tools-References.
' Then select Microsoft VBScript Regular Expressions 5.5
'
Sub TryThis()

Dim ptrn As String
Dim re As Object
Dim i As Integer

Set re = New RegExp

For i = 1 To 500
re.Pattern = Sheet1.Cells(i + 1, 2)
re.IgnoreCase = True
re.Global = True
Sheet1.Cells(i + 1, 1) = re.Replace(Sheet1.Cells(i + 1, 1), "")
Next

End Sub




In experimenting with the various solutions offered, I found that Robert Crandal's worked most effectively in all instances.

I had set up a table of items similar to the OP's request, but added a few mixed case's and combinations of text with numbers. In this one particular example:

PHRASE FIND & REPLACE
ex. fat123 777 777


The first two solutions, when activated, removed not just the '777' but also the 'fat' portion of 'fat123'. This occurred under the 'General', as well as 'Text' cell formattings

Only Mr. Crandals's code successfully replaced the phrase, leaving 'fat123' as would have been expected.

In Mr Busch's code, is it possible to alter the array to accomplish this?
I'm a novice to VBA but follow this group quite regularly; finding the solutions offered to be quite elegant, succinct and very informative.

Happy 2016!

Mark

Hi Mark,

Am Fri, 1 Jan 2016 10:28:06 -0800 (PST) schrieb :

In experimenting with the various solutions offered, I found that Robert Crandal's worked most effectively in all instances.

I had set up a table of items similar to the OP's request, but added a few mixed case's and combinations of text with numbers. In this one particular example:

PHRASE FIND & REPLACE
ex. fat123 777 777


The first two solutions, when activated, removed not just the '777' but also the 'fat' portion of 'fat123'. This occurred under the 'General', as well as 'Text' cell formattings

Only Mr. Crandals's code successfully replaced the phrase, leaving 'fat123' as would have been expected.

In Mr Busch's code, is it possible to alter the array to accomplish this?
I'm a novice to VBA but follow this group quite regularly; finding the solutions offered to be quite elegant, succinct and very informative.

please read the OP's question again. It is not totally clear. I guess
that the values in column B should be replaced in each match in column
A and not only in the same row.
If he would replace the value from B1 in A1, from B2 in A2 that could be
easily done with a formula.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Correct Claus !!

Regards,
Mandeep baluja