Delete Row when value inside cell is less than 0
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).Entire Row.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!!! -- bm446 ----------------------------------------------------------------------- bm4466's Profile: http://www.excelforum.com/member.php...fo&userid=3394 View this thread: http://www.excelforum.com/showthread.php?threadid=56834 |
Delete Row when value inside cell is less than 0
To delete rows with #REF! and #DIV/0! in them you can use this statement Columns("A").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete bm4466 Wrote: 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).Entire Row.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!!!! -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=568341 |
Delete Row when value inside cell is less than 0
Thanks a lot...do you know how to do it when the value is less than o equal to 0 -- bm446 ----------------------------------------------------------------------- bm4466's Profile: http://www.excelforum.com/member.php...fo&userid=3394 View this thread: http://www.excelforum.com/showthread.php?threadid=56834 |
Delete Row when value inside cell is less than 0
bm4466 Wrote: Thanks a lot...do you know how to do it when the value is less than or equal to 0? 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 -------------------- -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=568341 |
All times are GMT +1. The time now is 02:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com