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

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
"Bob" wrote:

> 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
>

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

"Bob" wrote:

> 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
>

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


"Bob" <(E-Mail Removed)> wrote in message
news:83E0B39C-49AA-49C0-9678-(E-Mail Removed)...
> 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
>

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
news:ORi$(E-Mail Removed)...
> 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
>
>
> "Bob" <(E-Mail Removed)> wrote in message
> news:83E0B39C-49AA-49C0-9678-(E-Mail Removed)...
>> 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
>>
>
>

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
> news:ORi$(E-Mail Removed)...
> > 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
> >
> >
> > "Bob" <(E-Mail Removed)> wrote in message
> > news:83E0B39C-49AA-49C0-9678-(E-Mail Removed)...
> >> 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
> >>
> >
> >
>
>
>

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


"Mike H" wrote:

> 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
>
> "Bob" wrote:
>
> > 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
> >