Delete rows

[Steph]

Can this code be modified so that ans = 2 things, "inactive" and "closed"?
The code currently finds the word "inactive" in column 12 and deletes the
row. I would like it to find both "inactive" and "closed", and delete those
rows. Thanks!

ans = "Inactive"
With Columns(12)
Do
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Loop While Not c Is Nothing
End With

 

[Guest]

There is really no advantage to doing an and since you are just doing a find.
You are only looking at matches so just run the code twice. It will do
exactly the dame amount of work. That is the beauty of doing .Find. If you
were traversing through Column 12 looking at each cell then that would be a
very different story. Looping twice in that case would take twice as long.

ans = "Inactive"
With Columns(12)
Do
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Loop While Not c Is Nothing
End With

ans = "Closed"
With Columns(12)
Do
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Loop While Not c Is Nothing
End With

HTH

 

[Guest]

Just repeat the loop a second time:

' YOUR CODE, then...
ans = "Closed"
With Columns(12)
Do
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Loop While Not c Is Nothing
End With

 

[Steph]

Simple enough. Thanks!

There is really no advantage to doing an and since you are just doing a find.
You are only looking at matches so just run the code twice. It will do
exactly the dame amount of work. That is the beauty of doing .Find. If you
were traversing through Column 12 looking at each cell then that would be a
very different story. Looping twice in that case would take twice as long.

ans = "Inactive"
With Columns(12)
Do
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Loop While Not c Is Nothing
End With

ans = "Closed"
With Columns(12)
Do
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Loop While Not c Is Nothing
End With

HTH

 

[Ron de Bruin]

Try
http://www.rondebruin.nl/delete.htm#Find