Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells method errors when criteria not found
The sub below is used to check the D column for values with a negative sign. If
the negative sign exists I want to delete the entire row. When there are no records with a negative sign in the D column, it returns a run-time error 1004 - No cells were found. How do I rewrite this procedure so that the error is not returned when no cells with a negative are found but still deletes rows when a value has a negative sign? I understand that some Range object methods require that the range contain data. If the range does not contain data, the method fails. But what would be the proper way around this? Sub CleanCancelledChks() Dim r As Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" Set r = r.SpecialCells(xlCellTypeVisible) .AutoFilterMode = False r.EntireRow.Delete End With End Sub Thank you in advance for any advice. mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells method errors when criteria not found
Sub CleanCancelledChks()
Dim r As Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" On Error Resume Next Set r = r.SpecialCells(xlCellTypeVisible) On Error goto 0 .AutoFilterMode = False if not rng is nothing then _ r.EntireRow.Delete End With End Sub -- Regards, Tom Ogilvy "ms" wrote in message ... The sub below is used to check the D column for values with a negative sign. If the negative sign exists I want to delete the entire row. When there are no records with a negative sign in the D column, it returns a run-time error 1004 - No cells were found. How do I rewrite this procedure so that the error is not returned when no cells with a negative are found but still deletes rows when a value has a negative sign? I understand that some Range object methods require that the range contain data. If the range does not contain data, the method fails. But what would be the proper way around this? Sub CleanCancelledChks() Dim r As Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" Set r = r.SpecialCells(xlCellTypeVisible) .AutoFilterMode = False r.EntireRow.Delete End With End Sub Thank you in advance for any advice. mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells method errors when criteria not found
In your specific case, resume to the next line on an error and if it i
error # 1004, exit the sub. If another error occurred (due t something other than not finding a match, display it and exit th sub). Sub CleanCancelledChks() Dim r As Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" On Error Resume Next Set r = r.SpecialCells(xlCellTypeVisible) .AutoFilterMode = False If Err.Number = 1004 Then Exit Sub Else Msgbox "Error Occurred: " & Err.Description Exit Sub End If r.EntireRow.Delete End With End Sub -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells method errors when criteria not found
Tom's answer is probably better. On Error Goto 0 simply turns th
normal error handling back on. -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells method errors when criteria not found
Thank you Tom and K.
There is something not right though. When testing with a set of records with no negative sign, r.EntireRow.Delete is deleting all records from row 2 down. The "If Not r is nothing then r.EntireRow.Delete" sees the statement as True and deletes all records except for the first line. Thank you, mark Tom Ogilvy wrote: Sub CleanCancelledChks() Dim r As Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" On Error Resume Next Set r = r.SpecialCells(xlCellTypeVisible) On Error goto 0 .AutoFilterMode = False if not rng is nothing then _ r.EntireRow.Delete End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells method errors when criteria not found
Try it with two range variables:
Sub CleanCancelledChks() Dim r As Range, Dim r1 as Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" On Error Resume Next set r1 = Nothing Set r1 = r.SpecialCells(xlCellTypeVisible) On Error goto 0 .AutoFilterMode = False if not r1 is nothing then _ r1.EntireRow.Delete End With End Sub -- Regards, Tom Ogilvy "ms" wrote in message ... Thank you Tom and K. There is something not right though. When testing with a set of records with no negative sign, r.EntireRow.Delete is deleting all records from row 2 down. The "If Not r is nothing then r.EntireRow.Delete" sees the statement as True and deletes all records except for the first line. Thank you, mark Tom Ogilvy wrote: Sub CleanCancelledChks() Dim r As Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" On Error Resume Next Set r = r.SpecialCells(xlCellTypeVisible) On Error goto 0 .AutoFilterMode = False if not rng is nothing then _ r.EntireRow.Delete End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells method errors when criteria not found
Not working for me
Add a zero in the D column and use this one Sub CleanCancelledChks2() Dim r As Range, r1 As Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="<0" On Error Resume Next Set r1 = Nothing Set r1 = r.SpecialCells(xlCellTypeVisible) On Error GoTo 0 .AutoFilterMode = False If Not r1 Is Nothing Then _ r1.EntireRow.Delete End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Tom Ogilvy" wrote in message ... Try it with two range variables: Sub CleanCancelledChks() Dim r As Range, Dim r1 as Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" On Error Resume Next set r1 = Nothing Set r1 = r.SpecialCells(xlCellTypeVisible) On Error goto 0 .AutoFilterMode = False if not r1 is nothing then _ r1.EntireRow.Delete End With End Sub -- Regards, Tom Ogilvy "ms" wrote in message ... Thank you Tom and K. There is something not right though. When testing with a set of records with no negative sign, r.EntireRow.Delete is deleting all records from row 2 down. The "If Not r is nothing then r.EntireRow.Delete" sees the statement as True and deletes all records except for the first line. Thank you, mark Tom Ogilvy wrote: Sub CleanCancelledChks() Dim r As Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" On Error Resume Next Set r = r.SpecialCells(xlCellTypeVisible) On Error goto 0 .AutoFilterMode = False if not rng is nothing then _ r.EntireRow.Delete End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells method errors when criteria not found
You are correct that the criteria supplied by the OP doesn't appear to
work - but since he implied that it did, I didn't check that. Thanks for the correction. -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Not working for me Add a zero in the D column and use this one Sub CleanCancelledChks2() Dim r As Range, r1 As Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="<0" On Error Resume Next Set r1 = Nothing Set r1 = r.SpecialCells(xlCellTypeVisible) On Error GoTo 0 .AutoFilterMode = False If Not r1 Is Nothing Then _ r1.EntireRow.Delete End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Tom Ogilvy" wrote in message ... Try it with two range variables: Sub CleanCancelledChks() Dim r As Range, Dim r1 as Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" On Error Resume Next set r1 = Nothing Set r1 = r.SpecialCells(xlCellTypeVisible) On Error goto 0 .AutoFilterMode = False if not r1 is nothing then _ r1.EntireRow.Delete End With End Sub -- Regards, Tom Ogilvy "ms" wrote in message ... Thank you Tom and K. There is something not right though. When testing with a set of records with no negative sign, r.EntireRow.Delete is deleting all records from row 2 down. The "If Not r is nothing then r.EntireRow.Delete" sees the statement as True and deletes all records except for the first line. Thank you, mark Tom Ogilvy wrote: Sub CleanCancelledChks() Dim r As Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" On Error Resume Next Set r = r.SpecialCells(xlCellTypeVisible) On Error goto 0 .AutoFilterMode = False if not rng is nothing then _ r.EntireRow.Delete End With End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells method errors when criteria not found
Thanks again all for your help. I was testing this out and it worked fine until
I did a test with only one record. "if not r1 is nothing then _" returned as True, though there was no negative sign in the D column, and deleted the record. I am not sure why "Set r1 = r.SpecialCells(xlCellTypeVisible)" no longer is equal to Nothing. I can not try Ron's idea where Criteria searches for <0 values rather than "-" because the negative symbol is at the end of whatever value is in the D column, (ex. 0045678-). Any suggestions? Thanks all, mark Tom Ogilvy wrote: Try it with two range variables: Sub CleanCancelledChks() Dim r As Range, Dim r1 as Range With ActiveSheet Set r = .Range(.Range("D2"), .Range("D" & _ Rows.Count).End(xlUp)) .Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*" On Error Resume Next set r1 = Nothing Set r1 = r.SpecialCells(xlCellTypeVisible) On Error goto 0 .AutoFilterMode = False if not r1 is nothing then _ r1.EntireRow.Delete End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile error: Method or data member not found | Excel Worksheet Functions | |||
Return all values found for criteria | Excel Discussion (Misc queries) | |||
If search criteria is not found, then goto Repeat? | Excel Programming | |||
Go to record found by Criteria in Data Form | Excel Programming |