Do Until Loop

[sachavez]

Need help with VBA code:

Need code to loop though worksheet and find specific text, i.e. "B A SMITH" and delete entire row.

The number of rows containing "B A SMITH" is different each day.

Thanks in advance!

Steve

 

[MrKowz]

Try:

Public Sub DeleteNames()
Dim rng     As Range
    
Application.ScreenUpdating = False
With Cells
    Set rng = .Find("B A Smith", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
    If Not rng Is Nothing Then
        Do
            rng.EntireRow.Delete
            Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing
    End If
End With
Application.ScreenUpdating = True
End Sub

 

[sachavez]

Works like a champ, thanks!, MrKowz!

Now, I have multiple names that I'm searching for and deleting entire row. Do I have to copy the entire code for each name, or is there a way to "find" all of the names and then do the loop?

Let me know if I'm not clear.

Thanks again!

 

[sachavez]

RESOLVED



OK, I just copied the code for each name; it works perfectly!

Thanks for the help!!!!

 

[MrKowz]

I could have sworn that I posted the other solution. Sorry about that - here it is - adjust the names in the nameary variable to fit all of the names you need:

Public Sub DeleteNames()
Dim rng     As Range, _
    nameary As Variant, _
    i       As Long
 
nameary = Array("B A Smith", "J A Nobody", "P A Edwards")
 
Application.ScreenUpdating = False
For i = LBound(nameary) To UBound(nameary)
    With Cells
        Set rng = .Find(nameary(i), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
        If Not rng Is Nothing Then
            Do
                rng.EntireRow.Delete
                Set rng = .FindNext(rng)
            Loop While Not rng Is Nothing
        End If
    End With
Next i
Application.ScreenUpdating = True
End Sub