Delete rows

  • Thread starter Thread starter Steph
  • Start date Start date
S

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
 
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
 
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
 
Simple enough. Thanks!

Jim Thomlinson said:
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
Click to expand...
 
Back
Top