Code to delete rows that don't meet criteria

[kc_native]

I am starting to write some code that will take a spreadsheet with thousands of rows of project data, and delete all rows except the rows that contain the name of a specific group of individuals. My first thought is to use a For loop, with an If(AND) Then statement like the following:

For lngRow = 1 to 5000
     If(Cells(lngRow,4)<>name1 AND Cells(lngRow1,4)<>name2 AND Cells(lngRow1,4)<>name3......) Then
          Rows(lngRow).Delete Shift:=xlUP
          lngRow = lngRow -1
     End If
Next lngRow

There has to be a more efficient way to do this, and I would appreciate any input I can get. This may very well work, but I don't know how many ANDs I can use in an If statement.

Thanks!

 

[pboltonchina]

Try changing the column references & values to suit your needs. Try on a copy of your data

  Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
   If (Range("A" & i) = "" And Range("B" & i) <> "*Group*" And Range("C" & i) <> "0") Then Rows(i).Delete
 Next i

 

[kc_native]

Try changing the column references & values to suit your needs. Try on a copy of your data

  Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
   If (Range("A" & i) = "" And Range("B" & i) <> "*Group*" And Range("C" & i) <> "0") Then Rows(i).Delete
 Next i

Thanks for that, China. Can you expand on what the *Group* signifies? How would that apply to a list on names I am wanting to exclude from the delete command. FYI, those names
appear in the Last, First format on the spreadsheet.

Thanks!

 

[pboltonchina]

It refers to the text you are looking for. I was looking for anything that containing the word Group, Hence the wild cards * front and back. Anything between the "" is the text you are looking for to set your deletion criteria.

 

[kc_native]

It refers to the text you are looking for. I was looking for anything that containing the word Group, Hence the wild cards * front and back. Anything between the "" is the text you are looking for to set your deletion criteria.

Okay, I was just wondering if there was a way to reference a group of names to compare to in the criteria, instead of comparing one name at a time, Which is what I did in the following code. It works, but it's not very code efficient. I am using Last# instead of the actual last names I am serching for, for illustration purposes.

For lngRow = Cells(Rows.Count, 1).End(xlUp).Row - 1 To 6 Step -1
    If (Left(Cells(lngRow, 8), 6) <> "Last1" And Left(Cells(lngRow, 8), 7) <> "Last2" And _
        Left(Cells(lngRow, 8), 7) <> "Last3" And Left(Cells(lngRow, 8), 5) <> "Last4" And _
        Left(Cells(lngRow, 8), 8) <> "Last5" And Left(Cells(lngRow, 8), 6) <> "Last6" And _
        Left(Cells(lngRow, 8), 5) <> "Last7" And Left(Cells(lngRow, 8), 7) <> "Last8" And) Then
            Rows(lngRow).Delete (xlUp)
    End If
Next lngRow

 

[pboltonchina]

My code looks for criteria in 3 columns and can be expanded to suit. Pm me your email address and then you can send the file for me to look at. Or you can post sample data here