DELETE ROW 3 MATCHING CRITERIA

[FIRSTROUNDKO via OfficeKB.com]

HI!

I am loooking fo a macro that will delete 2 rows based a delete being in the
2nd row and the data in col A ans B being the same.

i.e before

Car Bus net 1 USD
Car Bus delete NOFAIR
Car Train net 2 USD
Plane Walking net 5 USD
Plane Bus delete NOFAIR

after

Car Train net 2 USD
Plane Walking net 5 USD
Plane Bus delete NOFAIR

Thanks in Advance

Darren

--
Message posted via http://www.officekb.com

[Tom Ogilvy]

Sub deleterows()
Dim lastrow as Long, i as Long
set lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 1 step -1
if cells(i,1).value = cells(i,2).value and cells(i,3).value = "delete"
then
rows(i).delete
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"FIRSTROUNDKO via OfficeKB.com" wrote:

> HI!
>
> I am loooking fo a macro that will delete 2 rows based a delete being in the
> 2nd row and the data in col A ans B being the same.
>
> i.e before
>
> Car Bus net 1 USD
> Car Bus delete NOFAIR
> Car Train net 2 USD
> Plane Walking net 5 USD
> Plane Bus delete NOFAIR
>
> after
>
> Car Train net 2 USD
> Plane Walking net 5 USD
> Plane Bus delete NOFAIR
>
> Thanks in Advance
>
> Darren
>
> --
> Message posted via http://www.officekb.com
>

[Tom Ogilvy]

Sub deleterows()
Dim lastrow as Long, i as Long
set lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 1 step -1
if cells(i,1).value = cells(i,2).value and cells(i,3).value = "delete"
then
rows(i).delete
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"FIRSTROUNDKO via OfficeKB.com" wrote:

> HI!
>
> I am loooking fo a macro that will delete 2 rows based a delete being in the
> 2nd row and the data in col A ans B being the same.
>
> i.e before
>
> Car Bus net 1 USD
> Car Bus delete NOFAIR
> Car Train net 2 USD
> Plane Walking net 5 USD
> Plane Bus delete NOFAIR
>
> after
>
> Car Train net 2 USD
> Plane Walking net 5 USD
> Plane Bus delete NOFAIR
>
> Thanks in Advance
>
> Darren
>
> --
> Message posted via http://www.officekb.com
>

[Don Guillett]

try
Sub deleteifsame()
On Error Resume Next
For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If UCase(Cells(i, "c")) = "DELETE" And _
Cells(i - 1, 1).Value = Cells(i, 1).Value And _
Cells(i - 1, 2).Value = Cells(i, 2).Value Then
Cells(i - 1, 1).Resize(2).EntireRow.Delete
End If
Next i
End Sub


--
Don Guillett
SalesAid Software
[email protected]
"FIRSTROUNDKO via OfficeKB.com" <u15639@uwe> wrote in message
news:5fa64777931de@uwe...
> HI!
>
> I am loooking fo a macro that will delete 2 rows based a delete being in
> the
> 2nd row and the data in col A ans B being the same.
>
> i.e before
>
> Car Bus net 1 USD
> Car Bus delete NOFAIR
> Car Train net 2 USD
> Plane Walking net 5 USD
> Plane Bus delete NOFAIR
>
> after
>
> Car Train net 2 USD
> Plane Walking net 5 USD
> Plane Bus delete NOFAIR
>
> Thanks in Advance
>
> Darren
>
> --
> Message posted via http://www.officekb.com

[Don Guillett]

Tom read it correctly. Mine is comparing ROWS not COLUMNS

--
Don Guillett
SalesAid Software
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:%[email protected]...
> try
> Sub deleteifsame()
> On Error Resume Next
> For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
> If UCase(Cells(i, "c")) = "DELETE" And _
> Cells(i - 1, 1).Value = Cells(i, 1).Value And _
> Cells(i - 1, 2).Value = Cells(i, 2).Value Then
> Cells(i - 1, 1).Resize(2).EntireRow.Delete
> End If
> Next i
> End Sub
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "FIRSTROUNDKO via OfficeKB.com" <u15639@uwe> wrote in message
> news:5fa64777931de@uwe...
>> HI!
>>
>> I am loooking fo a macro that will delete 2 rows based a delete being in
>> the
>> 2nd row and the data in col A ans B being the same.
>>
>> i.e before
>>
>> Car Bus net 1 USD
>> Car Bus delete NOFAIR
>> Car Train net 2 USD
>> Plane Walking net 5 USD
>> Plane Bus delete NOFAIR
>>
>> after
>>
>> Car Train net 2 USD
>> Plane Walking net 5 USD
>> Plane Bus delete NOFAIR
>>
>> Thanks in Advance
>>
>> Darren
>>
>> --
>> Message posted via http://www.officekb.com
>
>

[FIRSTROUNDKO via OfficeKB.com]

Thanks Don

Don Guillett wrote:
>Tom read it correctly. Mine is comparing ROWS not COLUMNS
>
>> try
>> Sub deleteifsame()
>[quoted text clipped - 31 lines]
>>>
>>> Darren

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1