Do Until Loop, Loop help please

[NewUser2]

I am trying to run a ‘Do Until’ loop in my macro that will search for a word, if its found, select the whole row containing that word, delete it, find the next and repeat. So far I have:</SPAN></SPAN>

Do</SPAN></SPAN>
Cells.Find(What:="Find_Word", After:=ActiveCell, LookIn:=xlFormulas, _</SPAN></SPAN>
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _</SPAN></SPAN>
MatchCase:=False, SearchFormat:=False).Activate</SPAN></SPAN>
ActiveCell.Rows("1:1").EntireRow.Select</SPAN></SPAN>
ActiveCell.Activate</SPAN></SPAN>
Selection.Delete Shift:=xlUp</SPAN></SPAN>
ActiveCell.Offset(-1, 0).Select</SPAN></SPAN>
Loop</SPAN></SPAN>

It deletes the rows I want, but just gives me a ‘Run-time error’ once all of the rows have been deleted and when I try to add 'Do Until', or 'Loop Until', it doesn’t work…. </SPAN></SPAN>
I also don’t know what I would add as the 'Loop until'=(cant find the word??)</SPAN></SPAN>

Any suggestions?</SPAN></SPAN>

 

[VeBeYay]

Hi,

Might be easier with a For Next loop? This code assumes the list starts in A1 ( Cells(1,1) ) and it doesn't specify the worksheet so assumes the sheet is the active sheet.

Sub test()

Dim iLRow, i As Integer

iLRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

For i = 1 To iLRow
    If Cells(i, 1) = "Find_Word" Then
        Cells(i, 1).EntireRow.Delete
    End If
Next

End Sub

 

[NewUser2]

Might be easier but I tried inputting what you have there and it doesnt fully work... it finds and deletes some of the rows but not all of them.
Need this to run until they are all gone... Even if separated by a blank cell, or if the cursor is in another cell.

What about doing Find/Select All that match a criteria, then delete all, but delete all the whole rows... I am not sure how that would look in VBA, but maybe another option? Can someone help with this?

Thanks!

 

[VeBeYay]

Works for me even with blanks in the list (as long as they're not at the end of column A) and doesn't matter where the curser is as long as the sheet is active.
Anyway, try this using the Find function.

Sub WordSearchRowDelete()
    Dim rng1 As Range
    Dim sSrch As String
    Application.ScreenUpdating = False
    strSrch = "Find_Word"
    Do
        Set rng1 = Range("A:A").Find(strSrch, , xlValues, xlWhole)
        If Not rng1 Is Nothing Then
            rng1.EntireRow.Delete
        End If
    Loop Until rng1 Is Nothing
    Application.ScreenUpdating = True
End Sub