Find all cells with certain value and delete the whole row

[GP2006]

Hi,

I have a messy crystal reports dump in Excell and I want to delete the entire row of every cell which has the word "date" in it. I have this so far:

Sub Macro1()
Range("D1").Select
    Selection.Copy
    Selection.SpecialCells(xlCellTypeLastCell).Select
    Range(Selection, Cells(1)).Select
    If Not (Selection.Find(What:="date", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)) Is Nothing Then
    Cells.Find(What:="date", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
    Selection.EntireRow.Delete
    End If
End Sub

But this only does it for the first cell it finds with the word "date" in and then stops. I either need it to continu to the next cell until it has done it for all cells in the selection or if there's an easier way to select all cells at once and delete their rows that would be fine too. The word "date" can be in multiple cells in a row.

Thanks.

[mana]

Option Explicit


Sub test()
    Dim r As Range, u As Range
    
    For Each r In ActiveSheet.UsedRange.Rows
        If WorksheetFunction.CountIf(r, "date") > 0 Then
            If u Is Nothing Then
                Set u = r
            Else
                Set u = Union(u, r)
            End If
        End If
    Next
    If Not u Is Nothing Then
        u.Delete xlShiftUp
    End If

End Sub

マナ

[GP2006]

Thank you for replying. This works very well, however only when the exact value of the cell is "date", but in my case the values are the word "date" with an actual date behind it, which is different every time.
In the piece of code I posted in the opening post it says LookAt:=xlPart to find cells which have date in part of the value. Maybe I should have made that more clear from the start, sorry about that.

[mana]

If WorksheetFunction.CountIf(r, "date*") > 0 Then

[GP2006]

Thanks, but I guess I've got some kind of special situation here where the cell value displayed is different than the actual value so selecting on the word "date" doesn't work. Your code works perfectly for actual cell values but not in this case.
Excell.jpg

[mana]

Option Explicit


Sub test2()
    Dim r As Range, f As Range, u As Range
    
    Application.FindFormat.Clear
    Application.FindFormat.NumberFormat = """date""mm-dd-yy"
    
    For Each r In ActiveSheet.UsedRange.Rows
        Set f = r.Find(What:="*", SearchFormat:=True)
        If Not f Is Nothing Then
            If u Is Nothing Then
                Set u = r
            Else
                Set u = Union(u, r)
            End If
        End If
    Next
    If Not u Is Nothing Then
        u.Delete xlShiftUp
    End If
    
    Application.FindFormat.Clear
    
End Sub

[GP2006]

I get error 1004 in Application.FindFormat.NumberFormat = """date""mm-dd-yy"

[jolivanes]

Maybe attach a file for the people to try their code on. Pictures are very difficult to run code on.

[GP2006]

I figured it out. Would not have been able to without your help mana, thanks.