delete entire row on contiguous duplicates

[ninez87]

hi need help for a look that do

Run a loop cycle that searches for the word "Special" in column A1 (the cell a1 contains different words inside)
If true looking if b2 = "Quantity" deletes b1 row)


in red is what i need to be deleted
Thanks for help


A B
[TABLE="width: 464"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Special 175377.2 QG #LDP 2443 #AVP 4748
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]ECEPI443[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]EPILZ750103[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6AV21232DB030AX0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6ES72151AG400XB0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6ES72231BL320XB0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]ESI6ES79548LC020AA0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]EDATTHQQS00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Special 175427 QG #LDP 2442 #AVP 4726
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]ECEBGS8625[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ECEOCM50100BB07[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]EPILZ750103[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI3LD22130TK51[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6ES72151AG400XB0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6ES72231BL320XB0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6ES79548LC020AA0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6AV21232DB030AX0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ETMXB4BVB6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Special 175364 BD #LDP 2441 #AVP 4740
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]ETMA9F79102[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Special 175364 QG #LDP 2439 #AVP 4751
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]Special 175346 QG #LDP 2438 #AVP 4750
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]ECEOCM50100BB07[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]EPILZ750103[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI3LD22130TK51[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6ES72151AG400XB0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6ES72231BL320XB0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6ES79548LC020AA0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ESI6AV21232DB030AX0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ETMXB4BVB6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Special 175364 BD #LDP 2441 #AVP 4740
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]Special 175364 QG #LDP 2439 #AVP 4751
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]Special 175346 QG #LDP 2438 #AVP 4750
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]GIA IN ORDINE DA UFFICIO TECNICO - NON SERVE ORDINARE!!![/TD]
[TD]Quantity
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

The color codining is a little confusing, but give this a try.

Sub deleStuff()
Dim i As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
            If LCase(.Cells(i, 2).Value) = "quantity" And InStr(.Cells(i, 1), "Special") = 1 _
            And InStr(.Cells(i - 1, 1), "Special") = 1 Then
                .Rows(i).Delete
            End If
        Next
    End With
End Sub

Make a back up copy of your file to test this code on. Do not test it on your original file until you know it works.

 

[ninez87]

thanks for reply

tried but not work
not deleting rows , no error given

The color codining is a little confusing, but give this a try.

Sub deleStuff()
Dim i As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
            If LCase(.Cells(i, 2).Value) = "quantity" And InStr(.Cells(i, 1), "Special") = 1 _
            And InStr(.Cells(i - 1, 1), "Special") = 1 Then
                .Rows(i).Delete
            End If
        Next
    End With
End Sub

Make a back up copy of your file to test this code on. Do not test it on your original file until you know it works.

 

[Fluff]

What are the values you are searching for?
In the thread https://www.mrexcel.com/forum/excel...re-row-contiguous-duplicates.html#post4883877
you are looking for
Speciale & Quantita
rather than
Special & Quantity

 

[ninez87]

Special & Quantity

What are the values you are searching for?
In the thread https://www.mrexcel.com/forum/excel...re-row-contiguous-duplicates.html#post4883877
you are looking for
Speciale & Quantita
rather than
Special & Quantity

 

[JLGWhiz]

thanks for reply

tried but not work
not deleting rows , no error given

OK, I used your example data in the OP to test the macro and it worked on that. Don't know why it isn't working for you.

 

[Peter_SSs]

in red is what i need to be deleted

Near the bottom of your sample table, can you explain why 3 "Special ..." cells are red but only 1 of the "Quantity" cells is red?

 

[JLGWhiz]

@ninez87, try this modified version and see if it deletes the rows.

Sub deleStuff2()
Dim i As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
            If LCase(.Cells(i, 2).Value) = "quantity" And InStr(.Cells(i, 1), "Special") > 0 _
            And InStr(.Cells(i - 1, 1), "Special") > 0 Then
                .Rows(i).Delete
            End If
        Next
    End With
End Sub

This checks to see if 'Special' is anywhere in the column A Text. I didn't want to use that originally because I don't know what your total data looks like, but if there are leading spaces preventing the original code from working like you want, then this should fix that.

 

[ninez87]

thanks now works!!

 

[ninez87]

Near the bottom of your sample table, can you explain why 3 "Special ..." cells are red but only 1 of the "Quantity" cells is red?

my mistake..
problem is solved.
thanks all for time and help