Deleting rows based on values in a column
 

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

Without looping:

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

HTH,
Bernie
MS Excel MVP


"Bob" wrote in message
...
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

Deleting rows based on values in a column
 

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


"Bernie Deitrick" wrote:

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bob,

Without looping:

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

HTH,
Bernie
MS Excel MVP


"Bob" wrote in message
...
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