CODEWITHSUNDEEP
excelvbarows
soldier2gud4me
soldier2gud4me

Reputation: 79

If Cell equals Text then delete that row until end of range

Need help as i Cant find anything online to achieve deleting all rows in a range starting from any cell in that range that contains the value "X".

Dim srchRng As Range

Set srchRng = Range("A10:A308")

Dim c As Range
For Each c In srchRng
    If c.Value = "X" Then
        
       ' Delete rows starting with row containing X until A308
                  
        Exit For
    End If
Next

End Sub

Upvotes: 0

Views: 105

Answers (2)

norie
norie

Reputation: 9857

You could also do this without looping.

Dim srchRng As Range
Dim Res As Variant

    Set srchRng = Range("A10:A308")

    Res = Application.Match("X", srchRng, 0)

    If Not IsError(Res) Then
        With srchRng
           .Offset(Res - 1).Resize(.Rows.Count - Res + 1).EntireRow.Delete
        End With
    End If

Upvotes: 1

Jahanzaib Sehar
Jahanzaib Sehar

Reputation: 59

Dim srchRng As Range

Set srchRng = Range("A10:A308")

Dim c As Range
For Each c In srchRng
    If c.Value = "X" Then
        
   range(c, range("A308")).entirerow.delete
                  
        Exit For
    End If
Next

End Sub

Upvotes: 1

Related Questions