Delete Row when value inside cell is less than 0

  • Thread starter Thread starter bm4466
  • Start date Start date
B

bm4466

I was taught by a friend how to delete a row when a cell is blank wit
this script:

Sub DeleteBlankRows_1()
'This macro delete all rows with a blank cell in column A
On Error Resume Next 'In case there are no blank cells
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

but my problem is that I do not care for blank cells, I am onl
concerned with a cell who has a value that is not greater than 0. I
could be blank, it could be equal to 0, or it could be something lik
#REF! and #DIV/0!...somebody please Help!!!
 
To delete rows with #REF! and #DIV/0! in them you can use this
statement

Columns("A").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

I was taught by a friend how to delete a row when a cell is blank with
this script:

Sub DeleteBlankRows_1()
'This macro delete all rows with a blank cell in column A
On Error Resume Next 'In case there are no blank cells
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

but my problem is that I do not care for blank cells, I am only
concerned with a cell who has a value that is not greater than 0. It
could be blank, it could be equal to 0, or it could be something like
#REF! and #DIV/0!...somebody please Help!!!!
Click to expand...
 
bm4466 said:
Thanks a lot...do you know how to do it when the value is less than or
equal to 0?
Click to expand...


The only way I know is to loop through each value and test it like
this


Code:
--------------------
Sub DelRowVal()
'To be run after you have removed all blanks and errors from column A
Dim c As Range
Dim r As String

r = ""
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select

For Each c In Selection
If c.Value <= 0 Then
r = r + c.Address + ","
End If
Next c

If r <> "" Then
r = Mid(r, 1, Len(r) - 1)
Range(r).EntireRow.Select
Selection.EntireRow.Delete
Range("A1").Select
End If
End Sub
 
Back
Top