How to delete row based on cell value ?

How to delete row based on cell value?

Asked on October 26, 2018 in VBA.
Add Comment


  • 3 Answer(s)

    Use this code:

    Dim i As Long
    i = 1
    Do While i <= ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Rows.Count
    If InStr(1, ThisWorkbook.ActiveSheet.Cells(i, 1).Text, "-", vbTextCompare) > 0 Then
    
    ThisWorkbook.ActiveSheet.Cells(i, 1).EntireRow.Delete
    
    Else
    
    i = i + 1
    
    End If
    Loop
    End Sub
    
    Answered on October 26, 2018.
    Add Comment

    Use this code on new column:

    =IF(ISNUMBER(FIND("-",A1)),1,0)
    
    

    … then the sort on the column, at the rows where the value is 1 and delete them

    Answered on October 26, 2018.
    Add Comment

       User can loop through at the cells in our range and also use the InStr function and to check if a cell contains a string, in our case; a hyphen.

    Sub DeleteRowsWithHyphen()
    Dim rng As Range
      For Each rng In Range("A2:A10") 'Range of values to loop through
      If InStr(1, rng.Value, "-") > 0 Then 'InStr returns an integer of the position, if above 0 - It contains the string
    rng.Delete
    End If
    Next rng
    End Sub
    
    Answered on October 26, 2018.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.