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

[Clashcon]

[TABLE="width: 500"]
<tbody>[TR]
[TD]SKU
[/TD]
[TD]Type
[/TD]
[TD]KW1
[/TD]
[TD]KW2
[/TD]
[TD]KW3
[/TD]
[TD]SUM
[/TD]
[/TR]
[TR]
[TD]SKU1
[/TD]
[TD]Actual Sales
[/TD]
[TD]30
[/TD]
[TD]41
[/TD]
[TD]28
[/TD]
[TD]99
[/TD]
[/TR]
[TR]
[TD]SKU1
[/TD]
[TD]LY Sales
[/TD]
[TD]46
[/TD]
[TD]41
[/TD]
[TD]25
[/TD]
[TD]113
[/TD]
[/TR]
[TR]
[TD]SKU1
[/TD]
[TD]Forecst
[/TD]
[TD]40
[/TD]
[TD]42
[/TD]
[TD]43
[/TD]
[TD]125
[/TD]
[/TR]
[TR]
[TD]SKU2
[/TD]
[TD]Actual Sales
[/TD]
[TD]1084

[/TD]
[TD]793
[/TD]
[TD]2022
[/TD]
[TD]3899
[/TD]
[/TR]
[TR]
[TD]SKU2
[/TD]
[TD]LY Sales
[/TD]
[TD]950
[/TD]
[TD]958
[/TD]
[TD]765
[/TD]
[TD]2672
[/TD]
[/TR]
[TR]
[TD]SKU2
[/TD]
[TD]Forecst
[/TD]
[TD]871
[/TD]
[TD]790
[/TD]
[TD]1659
[/TD]
[TD]3320
[/TD]
[/TR]
[TR]
[TD]SKU3
[/TD]
[TD]Actual Sales
[/TD]
[TD]44
[/TD]
[TD]24
[/TD]
[TD]54
[/TD]
[TD]123
[/TD]
[/TR]
[TR]
[TD]SKU3
[/TD]
[TD]LY Sales
[/TD]
[TD]3
[/TD]
[TD]46
[/TD]
[TD]40
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]SKU3
[/TD]
[TD]Forecst
[/TD]
[TD]44
[/TD]
[TD]41
[/TD]
[TD]40
[/TD]
[TD]125
[/TD]
[/TR]
[TR]
[TD]SKU4
[/TD]
[TD]Actual Sales
[/TD]
[TD]655
[/TD]
[TD]379
[/TD]
[TD]284
[/TD]
[TD]1318
[/TD]
[/TR]
[TR]
[TD]SKU4
[/TD]
[TD]LY Sales
[/TD]
[TD]343
[/TD]
[TD]257
[/TD]
[TD]329
[/TD]
[TD]929
[/TD]
[/TR]
[TR]
[TD]SKU4
[/TD]
[TD]Forecst
[/TD]
[TD]641
[/TD]
[TD]265
[/TD]
[TD]342
[/TD]
[TD]1247
[/TD]
[/TR]
[TR]
[TD]SKU5

[/TD]
[TD]Actual Sales

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]SKU5
[/TD]
[TD]LY Sales
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1042
[/TD]
[TD]1042
[/TD]
[/TR]
[TR]
[TD]SKU5
[/TD]
[TD]Forecst
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]SKU6
[/TD]
[TD]Actual Sales
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SKU6
[/TD]
[TD]LY Sales
[/TD]
[TD]60
[/TD]
[TD]47
[/TD]
[TD]58
[/TD]
[TD]164
[/TD]
[/TR]
[TR]
[TD]SKU6
[/TD]
[TD]Forecst
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SKU7

[/TD]
[TD]Actual Sales

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]SKU7
[/TD]
[TD]LY Sales
[/TD]
[TD]66
[/TD]
[TD]71
[/TD]
[TD]47
[/TD]
[TD]184
[/TD]
[/TR]
[TR]
[TD]SKU7
[/TD]
[TD]Forecst
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.