Remove all rows after Value is found in column

Hey,

The macro I'm looking for should be fairly simple but everything I've tried isn't working and I don't know what I'm doing wrong.I need a macro to look at the name in: C2 and search from C3 down to end of page, the we only need to know when it first encounters the name. (C7 in my example) and then it needs to delete that row and other rows below .

So in this example it looks at C2 and finds James Low it searchs and finds James Low on C7 and then deletes the rows from 7 to 21 (A7:D21) (See attachment)

Thanks in advance
- Hyflex

Try this:

Sub x()
  
Dim rFind As Range
 
With Sheet1.Columns(3)
    Set rFind = .Find(What:=.Cells(2), After:=.Cells(2), LookAt:=xlWhole, SearchOrder:=xlByRows, _
                      SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        Range(rFind, .Cells(Rows.Count).End(xlUp)).EntireRow.Delete
    End If
End With
     
End Sub

Awesome, works perfectly.

Could I possibly ask for a similar macro but instead of looking at C2 it needs to look at B2 and when it needs to go down each cell until B finds a different value when it finds a different value to what is in B2 it deletes that row and all rows below it.

So in your example that would leave only row 2?

Originally Posted by StephenR

So in your example that would leave only row 2?

Yeah, the examples quite poor for the secondary macro.

With apologies to MarvinP...

Sub DeleteDupsRange()
    Dim LastRow As Double
    Dim RowCtr As Double
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For RowCtr = 3 To LastRow
        If Cells(2, "B") <> Cells(RowCtr, "B") Then
            Rows(RowCtr & ":" & LastRow).Delete
            Exit Sub
        End If
    Next RowCtr
End Sub