VBA to delete variable (n-Rows) from top lines of each Worksheet within Workbook

[Onceapirate]

Hi, (complete novice here)

I'd appreciate help, I've struggled on my own for too long today to make progress...

What I have: I've a Workbook containing multiple (almost 30) Worksheets - each sheet having a different Name but having somewhat different Header Structure. Common to all of these is that the client info I need starts below a cell in Column A that always contains text "Header Number". Sometimes this appears in Row 11 and sometimes in Row 14.

What I need: I want to delete all Rows from A1 down to and including the Row containing text "Header Number", so that each Worksheet will only have client info and No Headers. I am trying to get VBA code to loop through ALL sheets in the Workbook and delete these top 11 or top 14 Rows.

I have tried (below) which will work for the active sheet I am viewing, but I cannot get it to loop through all the other Sheets in the Workbook:

'--------------
Sub DelRows_down_to_Header_Number()
Dim r As Range
Dim ws As Worksheet

For Each Sh In ActiveWorkbook.Worksheets

Set r = Range("A1", "A" & Rows.Count).Find(What:="Header Number", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)

If Not r Is Nothing Then
Range("A1:A" & r.Row - 0).EntireRow.Delete

Else
MsgBox "No cell matching criteria found!"

End If
Next Sh

End Sub
'-----------------
Any help greatly appreciated!

 

[Georgiboy]

Welcome to the forum,

Couple of options below:

Sub test()
    Dim ws As Worksheet
    Dim fRow As Long
   
    On Error Resume Next
        For Each ws In Sheets
            fRow = ws.UsedRange.Find("Header Number", , , xlWhole).Row
            ws.Rows("1:" & fRow).Delete
            fRow = 0
        Next ws
    On Error GoTo 0
End Sub

Sub test2()
    Dim fRng As Range
    Dim ws As Worksheet
   
    For Each ws In Sheets
        Set fRng = ws.UsedRange.Find("Header Number", , , xlWhole)
        If Not fRng Is Nothing Then
            ws.Rows("1:" & fRng.Row).Delete
            Set fRng = Nothing
        End If
    Next ws
End Sub

 

[Onceapirate]

Welcome to the forum,

Couple of options below:

Sub test()
    Dim ws As Worksheet
    Dim fRow As Long
  
    On Error Resume Next
        For Each ws In Sheets
            fRow = ws.UsedRange.Find("Header Number", , , xlWhole).Row
            ws.Rows("1:" & fRow).Delete
            fRow = 0
        Next ws
    On Error GoTo 0
End Sub

Sub test2()
    Dim fRng As Range
    Dim ws As Worksheet
  
    For Each ws In Sheets
        Set fRng = ws.UsedRange.Find("Header Number", , , xlWhole)
        If Not fRng Is Nothing Then
            ws.Rows("1:" & fRng.Row).Delete
            Set fRng = Nothing
        End If
    Next ws
End Sub

Thank you so much - That has worked perfectly!

(hope I can pay it forward as my skills improve)