Find and replace XYZ but not when WXYZ

[D_N_L]

Hi all,

How do I delete an entire row based on cells in Column A that contain "Billy" without the speech marks.

Issue is that I may have other cells in the column which have the same name, i.e:

1. Billy Davis
2. Billy F
3. Billy

In the above I would just like the row of the last example to be deleted.

[Kaper]

dim rng as range 
set rng = Columns("A:").Find(What:="Billy", After:=Cells(rows.count, "A"), LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False)
if not rng is nothing then
  rng.entirerow delete
endif

if needed (probably yes) then do it in a loop (until rng is nothing)

[hafizimran]

Sub DelRows()
    Dim r As Range
    For Each r In Range("A1:A1000")
        If r.Text = "Billy" Then r.EntireRow.Delete
    Next
End Sub

[D_N_L]

Hi,

@Kaper
I receive a Compile error: Invalid use of property on this line:

rng.EntireRow Delete

@hafizimran

I couldn't get yours to work I'm afraid for the whole column. It kind of only applied to the last few cells and I had to keep on re-running it to work on them all.

[Kaper]

Hi Plumby,
of course there is missing dot between entirerow and delete
try full macro:

Sub test()
Dim rng As Range
Do
Set rng = Columns("A:A").Find(What:="Billy", After:=Cells(Rows.Count, "A"), LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False)
If Not rng Is Nothing Then
  rng.EntireRow.Delete
End If
Loop Until rng Is Nothing
End Sub

[nilem]

Hi Plumby,
maybe so

Sub ertert()
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
    .Replace "*Billy", True, xlWhole
'    .Replace "Billy", True, xlWhole
    .SpecialCells(2, 4).EntireRow.Delete
End With
End Sub

[D_N_L]

Thanks!! Both work.