Code to delete entire row if there is no negative value (highlighted red) across the row

[chanhh]

Hi, I am trying to write a VBA to delete unnecessary rows in my spreadsheet instead of doing it manually. Row range can be up to 1000+; column range is from A to R.

Negative values cells will be highlighted in red and they are spread out randomly from column (E:N). There could be 3 negative cells (highlighted red) in the same row or it can also contain no negative value across the whole row.

I need help with removing all rows that contain no negative values (red cell) across the whole row. *(Row will not be deleted If there is at least 1 negative value cell (highlighted red) in the row)

Thank you for your help!

 

[mikerickson]

Add a helper column with the formula =COUNTIF(E1:N1, "<0") then delete those rows with a 1 in the helper column.

If you want a VB routine

Dim i as Long

With Range("A:A")
    With Range(.Cells(1,1), .Cells(Rows.Count,1).End(xlUp))
        For i = .Rows.Count to 1 step -1
            With .Cells(i,1).EntireRow
                If WorksheetFunction.Countif(.Range("E1:N1"), "<0") = 0 Then .Delete
            End With
        Next i
    End With
End With

 

[chanhh]

Add a helper column with the formula =COUNTIF(E1:N1, "<0") then delete those rows with a 1 in the helper column.

If you want a VB routine

Dim i as Long

With Range("A:A")
    With Range(.Cells(1,1), .Cells(Rows.Count,1).End(xlUp))
        For i = .Rows.Count to 1 step -1
            With .Cells(i,1).EntireRow
                If WorksheetFunction.Countif(.Range("E1:N1"), "<0") = 0 Then .Delete
            End With
        Next i
    End With
End With

Thank you Mikerickson. That works perfect.