Delete all rows below certain value on multiple worksheets?

[smduello]

Hello,

I searched and tried myself, but to no avail on how to do this.

I want to delete all rows below a certain value on multiple worksheets
(I am importing multiple text files @ once).

I need to delete all rows below the phrase "detail as follows".

I can't just delete the rows by highlighting all worksheets at once as
each different worksheet has the phrase "detail as follows" on
different lines.

Any ideas?

 

[Ron de Bruin]

Try this for one worksheet (Sheet1), it look in the A column
If it is working OK then we can make a loop through all sheets

Sub Find_Test()
Dim FindString As String
Dim Rng As Range
FindString = "detail as follows"
With Sheets("Sheet1").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Rng.Parent.Rows(Rng.Row + 1 & ":" & Rng.Parent.Rows.Count).Delete
Else
MsgBox "Nothing found"
End If
End With
End Sub

 

[Ron de Bruin]

Here is a test example for all worksheets.
It will check the A column in eacg sheet this example

Sub Find_Delete_Test_All_Worksheets()
Dim FindString As String
Dim Rng As Range
Dim sh As Worksheet

FindString = "detail as follows"

For Each sh In ThisWorkbook.Worksheets
With sh.Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Rng.Parent.Rows(Rng.Row + 1 & ":" & Rng.Parent.Rows.Count).Delete
Else
MsgBox "Nothing found in sheet: " & sh.Name
End If
End With
Next sh
End Sub

 

[Ron de Bruin]

Forgot to add this:

This example must be in a module of the same workbook where the sheets are in.
You can change ThisWorkbook to ActiveWorkbook if you do it different in this line

For Each sh In ThisWorkbook.Worksheets