Search sheet for text string

Hi,
I recorded some code to find the text string "Bad Piece" and delete cell next to it.
    Cells.Find(What:="Bad Piece", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0,1).Delete

Open in new window

What I cannot manage to do is amend the code so that it continues searching for the same text string until it reaches the end of the data.
Unfortunately, the datasheet is downloaded from a client and has many blank cells and sometimes columns.
The term "Bad Piece" can be found in a number of rows and can also be in different columns. I know that "Ctrl-End" gets to the last cell of the data irrespective of gaps, but I cannot fathom how to iterate through the sheet to find all the instances of "Bad Piece" and delete the cell to the right of it.
As always any help is appreciated.
LVL 1
Stephen ByromWarehouse/ShippingAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Did you want to clear the adjacent cell (as Dreamboat was doing) or delete it?

The following code will delete the adjacent cells, moving the remaining cells up. I put a loop counter in there because I had an infinite loop the first time I tested.
Sub BadPieces()
Dim cel As Range
Dim addr As String
Dim i As Long
Application.ScreenUpdating = False
Set cel = Cells.Find(What:="Bad Piece", After:=Cells(Rows.Count, Columns.Count), LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)
If cel Is Nothing Then Exit Sub
addr = cel.Address
Do
    i = i + 1
    cel.Offset(0, 1).Delete
    Set cel = Cells.FindNext(cel)
    If cel.Address = addr Then Exit Do
    If i > 1000 Then Exit Do    'Prevents infinite loops
Loop
End Sub

Open in new window

0
 
Anne TroyConnect With a Mentor East Coast ManagerCommented:
Since no one answered in the last hour, I'll throw this out there for you:

This is the link:

http://www.mrexcel.com/forum/excel-questions/80940-delete-cells-based-text-value-adjacent-cell.html

this is the code:

Sub Deleter1()

Lastrow = Range("A65536").End(xlUp).Row

For x = Lastrow To 1 Step -1
If Range("A" & x).Value = "Test" And Range("B" & x).Value = 0.75 Then
Range("A" & x, "B" & x).ClearContents
End If
Next x

End Sub

Open in new window


Since you're in 2010 with a million-some rows, you'll want to change the A65536 to A1048576. And you'll want to change "Test to "Bad Data", and you'll probably want to delete the 2nd part of the argument, giving you

Sub Deleter1()

Lastrow = Range("A1048576").End(xlUp).Row

For x = Lastrow To 1 Step -1
If Range("A" & x).Value = "Bad Data" Then
Range("B" & x).ClearContents
End If
Next x

End Sub

Open in new window


Change columns A and B to your respective columns. This tested fine for me.
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks to you both.
I have split the points as both solutions work the way I want with a bit of adjustment.
Thanks to you both for your time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.