Find specific text in column and delete all rows above

[bradyj7]

Hi all,

I'm trying to write a macro to find the text "Journey Start Event" in column e and delete all rows above this. There will only be one instance of this text. I've adapted the macro below from another one that detects the first instance of a text and deletes all rows above. However I cannot seem to get it to work and there are no errors. Any ideas?

Here is where I got the original code http://excel.bigresource.com/Track/excel-XkKkmdSJ/

Sub Deleterowsabove()
Dim foundOne As Range
On Error Resume Next
With ActiveSheet
    Set foundOne = .Range("E:E").Find(What:="Journey Start Event", After:=.Range("e1"), LookIn:=xlFormulas, _
                                LookAt:=xlPart, SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, MatchCase:=False)
    If foundOne.Row = 1 Then
        Range(.Range("e1"), foundOne.Offset(-1, 0)).EntireRow.Delete shift:=xlUp
    Else
    
End If
End With
On Error GoTo 0
End Sub

 

[VoG]

Try

If foundOne.Row > 1 Then

 

[bradyj7]

Hi that works thanks.

I don't really understand why it should be > 1 if that text only appears once. But at least it works.

Would you know how to modify it to not delete row 1 as this rows has headings that I want to keep.

Thank you

 

[VoG]

Try

    If foundOne.Row > 2 Then
        Range(.Range("e2"), foundOne.Offset(-1, 0)).EntireRow.Delete shift:=xlUp
    End If

 

[bradyj7]

No that still deletes all rows above?

 

[VoG]

This left row 1 intact for me

Sub Deleterowsabove()
Dim foundOne As Range
On Error Resume Next
With ActiveSheet
    Set foundOne = .Range("E:E").Find(What:="Journey Start Event", After:=.Range("e1"), LookIn:=xlFormulas, _
                                LookAt:=xlPart, SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, MatchCase:=False)
    If foundOne.Row > 2 Then
        Range(.Range("e2"), foundOne.Offset(-1, 0)).EntireRow.Delete shift:=xlUp
    End If
End With
On Error GoTo 0
End Sub

 

[bradyj7]

Hi, this works, thanks for the help and quick reply.

John