Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 | |
|
|
![]() |
||||
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 |