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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Little Penny" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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
Little Penny wrote:
> 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