Do Until Nothing Found

Question

I am working on a macro to go through stock data. When no data is available on a given date, the data shows "-" in those rows. I need to delete those rows. I have come up with a macro that deletes the first row found. I need to keep it going until all rows with "-" are deleted.

How can I do this with a Do Until loop?

Sub removejank()
'
' removejank Macro
    Cells.Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas2, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        
    If ActiveCell.Value = "-" Then
        Rows(ActiveCell.Row).Delete
    End If        
End Sub

Answer 1

There are many possible ways to loop through and delete rows.

In your example, using Activate significantly slows down runtime when you are looping data. .Activate is almost completely avoidable in just about every circumstance. Obviously, I see that what you've done was with the macro recorder, so it's understandable that it's there.

Anyway, try the following code. This only looks in column A. If you need to look in a different column, then change the "1" in this line:

If .Cells(r, 1).Value = "-" Then

To the column # you want to look in. Also, you will likely need to change the sheet name in this line:

With ThisWorkbook.Worksheets("Sheet3")

to match the sheet name you are wanting the code to run in.

---

So the way this method of the many methods out there works is that you will loop through every line in column A with For r = 1 .... Each loop iteration you are looking in the cell's value. If it equals -, then the If statement is true and proceeds to add the cell to our special range that we named delRng. You are not actually deleting the row just yet, only tracking them in this range.

Once finished, you will take all the single cells in the delRng, and using the .EntireRow property, delete them all at once. This is generally faster than deleting rows one at a time.

Public Sub RemoveJunk()

    Dim delRng As Range, r As Long
    With ThisWorkbook.Worksheets("Sheet3")
        For r = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If .Cells(r, 1).Value = "-" Then
                If delRng Is Nothing Then
                    Set delRng = .Cells(r, 1)
                Else
                    Set delRng = Union(delRng, .Cells(r, 1))
                End If
            End If
        Next r
    End With
    
    If Not delRng Is Nothing Then delRng.EntireRow.Delete

End Sub