Delete Rows Not Meeting Criteria

viktora · Feb 27th 2007

How can I automatically find and delete rows with cells that DO NOT meet multiple text criteria with a macro or VBA code? In other words, my data includes 2 columns of names and a header row. There are many names (20+) but I only need to keep the rows that have any one of 5 names in either of the 2 columns.

JF · Feb 27th 2007

Re: Delete Rows Not Meeting Criteria

Are the 5 names static or do they need to change?

If they are static, you could set up a named range and then use a formula to check the columns.

You could use a working column and a formula to indicate which rows to keep.

So if your names are in column A & B list your desired names in J1-J5

then use the following formula in Col C:

=IF(AND(ISERROR(MATCH(A1,$J$1:$J$5,0)),ISERROR(MATCH(B1,$J$1:$J$5,0))),"N","Y")

This could be copied down Either manually or via vba, then sort the list and remove any lines with a "N" in col C

You can do it via vba as follows:

Code

Sub DeleteLines()
'
'
tmp = Range("a65536").End(xlUp).Row
Range("c1").Select




With Range("C1:C" & tmp)
    .FormulaR1C1 = _
        "=IF(AND(ISERROR(MATCH(R[]C[-2],R1C10:R5C10,0)),ISERROR(MATCH(R[]C[-1],R1C10:R5C10,0))),""N"",""Y"")"
        .Copy
        .PasteSpecial Paste:=xlPasteValues
End With


'Delete rows with "N"


 On Error Resume Next
    Columns("C:C").Select
  SR1 = Selection.Find(What:="N", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Row
        
 On Error GoTo 0
  If SR1 <> 0 Then Rows(SR1 & ":" & tmp).Delete Shift:=xlUp


0:
Application.ScreenUpdating = True
End Sub

Display More

Delete Rows Not Meeting Criteria

Participate now!

Share