Deletion of rows in excel

[frankwhite]

Hi

I was hoping someone could help me out here please, basically I have rows and rows of data in Excel, in column H some cells start with __INVALID - so would it be possible to run a macro which would delete the whole row? So basically search and delete all rows where column c = __INVALID.

Thanks

[Siddharth Rout]

Sub FindText()
    Dim rng1 As Range, cell As Range, SrchString As String
    
    Application.ScreenUpdating = False
    
    '~~> Change this to the relevant Column
    Set rng1 = Range("A:A")
    SrchString = "__INVALID"
 
    For Each cell In rng1
        '~~> I am searching for words starting with "__INVALID"
        '~~> Change this as per your requirememnt. For example
        '~~> If you want to check for "__INVALID" at any position
        '~~> in the cell then use Instr()
        If UCase(Left(Trim(cell.Value), 9)) = SrchString Then
            '~~> Delete Row
            Rows(cell.Row).Delete Shift:=xlUp
        End If
    Next
    
    Application.ScreenUpdating = True
End Sub