Deleting rows based on values in a column

[Guest]

I'm trying to write a macro that I can embed within an existing subroutine
that will delete an entire row if the value in column D = "Completed" OR
"Cancelled". Although the number of rows in the worksheet can vary from day
to day (I download an updated worksheet from a system daily), I would prefer
to use a macro that does not utilize looping (if possible). Instead, I
already know the total number of rows containing data based on the following
two lines of code:

Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Any help would be greatly appreciated. Thanks.
Bob

 

[Guest]

Try this:-

Sub serviant()
Dim myRange As Range
Dim iLastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("D1" & LastRow)
For Each c In myRange
c.Select
If c.Value = "Cancelled" Or c.Value = "Completed" Then
ActiveCell.EntireRow.Select
Selection.Delete
End If
Next
End Sub

Mike

 

[Guest]

Opps,

That doesn't wrk if 2 adjacent rows are both complete, it will have to be
done baxkwards:-

Sub traction()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1
Cells(i, 4).Select
If ActiveCell.Value = "Cancelled" Or ActiveCell.Value = "Completed" Then
ActiveCell.EntireRow.Select
Selection.Delete
End If
Next
End Sub

Mike

 

[Bernie Deitrick]

Bob,

Without looping:

Sub Bob()
Range("D").AutoFilter Field:=1, _
Criteria1:="=Completed", Operator:=xlOr, _
Criteria2:="=Cancelled"
Range("D265536").SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("D").AutoFilter
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

I forgot to add that I assumed that you have a heading in row 1 of column D that you want to keep.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,
Your solution is excellent . . . and without looping! You assumed correctly
that row 1 has heading labels (I should have mentioned that). Thanks for all
your help! I greatly appreciate it.
Bob

 

[Guest]

Mike,
Thanks for your solution. I was hoping, however, that it could be done
without looping.
Bob