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

Special 175377.2 QG #LDP 2443 #AVP 4748	Quantity
ECEPI443	1
EPILZ750103	1
ESI6AV21232DB030AX0	1
ESI6ES72151AG400XB0	1
ESI6ES72231BL320XB0	2
ESI6ES79548LC020AA0	1
EDATTHQQS00	1
Special 175427 QG #LDP 2442 #AVP 4726	Quantity
ECEBGS8625	1
ECEOCM50100BB07	1
EPILZ750103	1
ESI3LD22130TK51	1
ESI6ES72151AG400XB0	1
ESI6ES72231BL320XB0	1
ESI6ES79548LC020AA0	1
ESI6AV21232DB030AX0	1
ETMXB4BVB6	1
Special 175364 BD #LDP 2441 #AVP 4740	Quantity
ETMA9F79102	1
Special 175364 QG #LDP 2439 #AVP 4751	Quantity
Special 175346 QG #LDP 2438 #AVP 4750	Quantity
ECEOCM50100BB07	1
EPILZ750103	1
ESI3LD22130TK51	1
ESI6ES72151AG400XB0	1
ESI6ES72231BL320XB0	1
ESI6ES79548LC020AA0	1
ESI6AV21232DB030AX0	1
ETMXB4BVB6	1
Special 175364 BD #LDP 2441 #AVP 4740	Quantity
Special 175364 QG #LDP 2439 #AVP 4751	Quantity
Special 175346 QG #LDP 2438 #AVP 4750	Quantity
GIA IN ORDINE DA UFFICIO TECNICO - NON SERVE ORDINARE!!!	Quantity

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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