Check each row for criteria and delete that row and the 2 below it

[Clashcon]

SKU	Type	KW1	KW2	KW3	SUM
SKU1	Actual Sales	30	41	28	99
SKU1	LY Sales	46	41	25	113
SKU1	Forecst	40	42	43	125
SKU2	Actual Sales	1084	793	2022	3899
SKU2	LY Sales	950	958	765	2672
SKU2	Forecst	871	790	1659	3320
SKU3	Actual Sales	44	24	54	123
SKU3	LY Sales	3	46	40	150
SKU3	Forecst	44	41	40	125
SKU4	Actual Sales	655	379	284	1318
SKU4	LY Sales	343	257	329	929
SKU4	Forecst	641	265	342	1247
SKU5	Actual Sales	0	0	0	0
SKU5	LY Sales			1042	1042
SKU5	Forecst	5			5
SKU6	Actual Sales
SKU6	LY Sales	60	47	58	164
SKU6	Forecst
SKU7	Actual Sales	0	0	0	0
SKU7	LY Sales	66	71	47	184
SKU7	Forecst

<tbody>
</tbody>

Hi

I'm currently trying to write a macro which should look for each row where actual sales = 0 and then delete that one aswell as the 2 rows below it (e.g same SKU).

I tried several appraoches (if function, filter and extend range etc), however all of them eventually failed.

Above you can find an example of my data:
As you can see I highlighted the cells which are determine which rows have to be delete and the 2 below since they belong to the same SKU.

Many thanks for your support!

 

[Eric W]

Welcome to the Board!

Should SKU6 also be deleted since it has 0 actual sales?

Here's a macro that should do what you want.

Open a COPY of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM. Paste the following code into the sheet that opens:

Sub DelRows()
Dim MyData As Variant, r As Long, DelRange As Range


    MyData = Range("A1:F" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    Set DelRange = Nothing
    For r = 2 To UBound(MyData) Step 3
        If MyData(r, 6) = 0 Then
            If DelRange Is Nothing Then
                Set DelRange = Range(r & ":" & r + 2)
            Else
                Set DelRange = Union(DelRange, Range(r & ":" & r + 2))
            End If
        End If
    Next r
    DelRange.Delete
        
End Sub

Press Alt-Q to close the editor. Press Alt-F8, select DelRows, and click Run. Let us know if this works for you.

 

[Clashcon]

Hi Eric

Thank you so much! Works like a charm!

Greatly appreciated

 

[Eric W]

Glad to help. Thanks for the feedback.