Excel Macro to find the next number 1 in Column A:A

The following macro searches Column A for the first cell containing the number 1. Once found, it deletes all rows above that cell, so the row containing 1 becomes the new Row 1.

Sub DeleteRowsAboveFirstOne()

    Dim ws As Worksheet
    Dim foundCell As Range

    Set ws = ActiveSheet

    Set foundCell = ws.Columns("A").Find( _
        What:=1, _
        After:=ws.Cells(ws.Rows.Count, "A"), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

    If Not foundCell Is Nothing Then
        If foundCell.Row > 1 Then
            ws.Rows("1:" & foundCell.Row - 1).Delete
        End If
    Else
        MsgBox "The number 1 was not found in Column A.", vbInformation
    End If

End Sub

To run the macro, press Alt + F11 in Excel, select Insert > Module, paste the code into the module, and run DeleteRowsAboveFirstOne.

Note that the macro searches for a cell containing the exact numeric value 1, not a number such as 10 or 21. It deletes entire worksheet rows above the first match, preserving the row containing 1 and all rows below it.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin