CODEWITHSUNDEEP
vba
Gupta
Gupta

Reputation: 1

How do i search a word in excel and delete entire row but just once

Someone please help. I'm trying to write a VBA code that searches for a particular word "K00101" in my excel worksheet "Sales column "c" and then delete the entire row. There are lots of occurrences of the particular word in the worksheet but I just want to delete only one while seaching from bottom to top. My problem is that code is deleting all the rows and I want to stop after 1 delete..

With Sheets("Sales")
        Firstrow = .UsedRange.Cells(1).row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).row

        For Lrow = Lastrow To Firstrow Step -1
            With .Cells(Lrow, "C")
                If Not IsError(.Value) Then
                    If .Value = "T00106DSG5K95" Then .EntireRow.Clear              
            End If
          End With
        Next Lrow
     End With

Upvotes: 0

Views: 46

Answers (1)

YowE3K
YowE3K

Reputation: 23974

If you want to exit out of your loop at any point, you can use Exit For

With Sheets("Sales")
    Firstrow = .UsedRange.Cells(1).row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).row

    For Lrow = Lastrow To Firstrow Step -1
        With .Cells(Lrow, "C")
            If Not IsError(.Value) Then
                If .Value = "T00106DSG5K95" Then
                    .EntireRow.Clear
                    Exit For
                End If
            End If
        End With
    Next Lrow
End With

Upvotes: 1

Related Questions