Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weird: inside of a cell I have a small box or cell I can't delete. | Excel Discussion (Misc queries) | |||
CAN I TAB INSIDE A CELL | New Users to Excel | |||
Entering digits in a cell with numbers already inside cell | Excel Worksheet Functions | |||
I need to delete duplicates inside of spreadsheet | New Users to Excel | |||
how do i set up a cell to autoformat to the text inside the cell? | Excel Discussion (Misc queries) |