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

chanhh

New Member
Joined
Feb 15, 2019
Messages
4
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!
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,904
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
Code:
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
 
Last edited:

chanhh

New Member
Joined
Feb 15, 2019
Messages
4
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
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top