Find and Delete

[rkeane7]

Hi,

i am looking to create a macro that will find a specific word, if the word is found I would like it to delete that row and all rows below it.

I have wrote the following but this only delete the row where the word has been found.

Sub Delete()
    
    Dim rFound As Range

    On Error Resume Next
    
    With Sheet1
    
    Set rFound = .Columns(1).Find(What:="Hello", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("A3").Select
    End With
End Sub

Could any1 please help?

Thanks in advance.

[Stuie]

Hi rkeane7,

I would take a slightly different approach myself.

Give this a try:

Dim c As Range

For Each c In Range("A:A")
    If c.Value = "hello" Then
        Range(c.Address & ":A65536").EntireRow.Delete
    End If
Next c

i havnt been able to test this code but give it a try and if not let me know

[royUK]

Try this

    Dim rFound As Range
    Dim rDelete As Range
    '    On Error Resume Next
    With Sheet2
        Set rFound = .Columns(1).Find(What:="Hello", LookIn:=xlValues)

        If Not rFound Is Nothing Then
            Set rDelete = .Range(rFound, rFound.End(xlDown))
            rDelete.EntireRow.Delete
        End If
    End With

[rkeane7]

Thanks guys, ill give them a go.