Delete Specific Row up to a certain word

Need Help: Need VBA Code to Delete rows B5 to specific word for various worksheets.

Thanks
Jesse

This code worked below BUT need to keep the word"Test"

Sub EF921892()
Dim rngFound As Range
Set rngFound = Range("B:B").Find(what:="Test")
If Not rngFound Is Nothing Then
Range(Range("B5"), rngFound).EntireRow.Delete
End If
End Sub

Maybe a mock-up attachment with before and after will help.

How do I add in the code below various worksheets names with the same word "test" that needs to be deleted on those sheets?


Sub EF921892()
Dim rngFound As Range
Set rngFound = Range("B:B").Find(what:="Test")
If Not rngFound Is Nothing Then
Range(Range("B5"), rngFound.Offset(-1, 0)).EntireRow.Delete
End If
End Sub

Thank You Very Much
Jesse

Holger - Thats exactly what i needed. Thanks im going to try to understand this code.

Jesse

Lets say if i accidentally ran the Macro again, how do i stop it from deleting the rows above B1:B5, leaving the word test? Thanks..

got it thanks Holger. Do you recommend any tutorials for learning vba?

Thanks Holger,

One last one, yeah right. I searched all over the web and played with it but no luck. I have three the following words in a row, I want to delete the word other two rows(Test Sample & Test Second Sample) and keep the word test, how do i do that?

Test Sample
Test Second Sample
Test

The code as you have provided it:

Sub EF921892_1a()
Dim rngFound As Range
Dim ws As Worksheet
For Each ws In Worksheets
With ws
Set rngFound = .Range("B:B").Find(what:="Test")
If Not rngFound Is Nothing And rngFound.Row > 5 Then
.Range(.Range("B5"), rngFound.Offset(-1, 0)).EntireRow.Delete
End If
End With
Next ws
End Sub

Thanks, Jesse