Delete Rows down to a String

[pioshelby1980com]

Hi,

I have an excel sheet. I want to clear the contents from row 6 and then delete all the rows below that row until the row where the string "Hello World" is found in column B.

When I run the code, it clears the contents of row 6, as I want, but then it leave two of the rows between row 6 and the row that contains the string "Hello World" and then deletes rows further down the sheet instead. I can't see what the issue is. Can anyone help please?

Sub ClearContentsAndDeleteRows()
    Dim ws As Worksheet
    Dim startRow As Long, endRow As Long
    Dim endString As Long


    ' Set the target worksheet
    Set ws = ActiveSheet ' Change this if you want to specify a different sheet


    ' Find the row where "Total Invoiced To Date..." is located in Column B
    On Error Resume Next
    'endRow = ws.Columns(2).Find("Hello", , xlValues, xlWhole).Row
    'endString = "Hello"
    endRow = Application.WorksheetFunction.Match("Hello", ws.Columns(2), 0)
    On Error GoTo 0


    ' Check if the string was found
    If endRow > 0 Then
        ' Clear contents from row 6
        ws.Rows(6).ClearContents


        ' Delete all rows below row 6 until the row with the target string is found
        For startRow = 7 To endRow - 1
            ws.Rows(startRow).Delete
        Next startRow
    Else
        MsgBox "String not found in Column B.", vbExclamation
    End If
End Sub

 

[mumps]

Try:

Sub ClearContentsAndDeleteRows()
    Application.ScreenUpdating = False
    With ActiveSheet
        .Rows(6).ClearContents
        .Rows("7:" & Range("B:B").Find("Hello World").Row).Delete
    End With
    Application.ScreenUpdating = True
End Sub

 

[pioshelby1980com]

Brilliant Mumps. Thank you.

 

[mumps]

You are very welcome.

 

[pioshelby1980com]

Can you alter that not to delete the row with "Hello World" but to stop at the row above?

 

[mumps]

Easily done. Note the number in red.

Sub ClearContentsAndDeleteRows()
    Application.ScreenUpdating = False
    With ActiveSheet
        .Rows(6).ClearContents
        .Rows("7:" & Range("B:B").Find("Hello World").Row - 1).Delete
    End With
    Application.ScreenUpdating = True
End Sub

 

[pioshelby1980com]

Thank you again Mumps. You make it look so easy.

 

[mumps]

My pleasure.