Delete Rows based on cell values.

[Little Penny]

I am trying to expand on my simple macro to:

Look in column B and if it finds the value "No Employee" to select
that row and delete it. I also would like look in column C for values
that are 500 or less and delete that row.

My data could be as little as 75 rows to several hundred rows. And
these values could appear several times in there respective columns.
I'm not sure how to get the code to delete multiple instances.


Sub RemoveEmployee()
If Range("B5").Value = "No Employee" Then

Rows("5:5").Select
Selection.Delete Shift:=xlUp
End If
End Sub

 

[Don Guillett]

One way is one at a time such as

for i=cells(rows.count,"b").end(xlup).row to 2 step -1
if cells(i,"b")="No Employee" or cells(i,"c")<500 then rows(i).delete
next

 

[JW]

This will start at the last row containing data in column B and run
backwards to row #2. At anytime during the cycle, if column B of the
row = "No Employee" or column C of the row is less than or equal to
500, the row will be deleted.
Sub RemoveEmployee()
Dim r As Long
For r = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Cells(r, 2).Text = "No Employee" Or _
Cells(r, 3).Value <= 500 Then Rows(r).EntireRow.Delete
Next r
End Sub