VBA delete rows below last appareance of a string

[cazdealer]

Hi
I would like to put a string in sheet cell A1 of sheet ''info'' and then go to sheet ''data'', find the last appareance of that word in column A and delete all rows below.
if the string isnt there, a box opened saying ''name not found''
How could I do that with VBA?


Also, How I could do the opposite... finding the first appareance of that word and delete all rows above it? (still in column A)
thanks

 

[mikerickson]

On Error Goto ErrorOutWith Sheet("data").Cells
    Range(.Find(Sheet("Info").Range("A1").Text, After:=.Cells(1,1), searchDirection:=xlPrevious), .Cells(.Rows.Count,.Columns.Count)).EntireRow.Delete
End With
On Error Goto 0
Exit Sub
ErrorOut:
    MsgBox "not found"
    Err.Clear
    On Error Goto 0
Exit Sub

 

[VoG]

Try

Sub atest()
Dim LR As Long, Found As Range
With Sheets("Data")
    LR = .Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set Found = .Columns("A").Find(what:=Sheets("info").Range("A1").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Found Is Nothing Then
        MsgBox "Not found", vbInformation
    Else
        .Range("A" & Found.Row + 1 & ":A" & LR).EntireRow.Delete
    End If
End With
End Sub

Edit: for the opposite

Sub atest()
Dim Found As Range
With Sheets("Data")
    Set Found = .Columns("A").Find(what:=Sheets("info").Range("A1").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Found Is Nothing Then
        MsgBox "Not found", vbInformation
    Else
        .Range("A1:A" & Found.Row - 1).EntireRow.Delete
    End If
End With
End Sub

 

[cazdealer]

thanks for the Help guys but both code for removing the rows after the last appearance of the string don't work.

The one Mike provided don't work at all, and the one Peter provided delete all row below the first appearance of that string.

thanks Peter for the second code (to delete rows above), it works.

 

[VoG]

I missed the last bit. Try

Sub atest()
Dim LR As Long, i As Long, Found As Range
With Sheets("Data")
    LR = .Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    For i = LR To 1 Step -1
        If .Range("A" & i).Value = Sheets("info").Range("A1").Value Then
            Set Found = .Range("A" & i)
            Exit For
        End If
    Next i
    If Found Is Nothing Then
        MsgBox "Not found", vbInformation
    Else
        .Range("A" & Found.Row + 1 & ":A" & LR).EntireRow.Delete
    End If
End With
End Sub

 

[cazdealer]

thanks!