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

Clashcon

New Member
Joined
Aug 30, 2019
Messages
2
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,145
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:
Code:
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.
 

Forum statistics

Threads
1,085,169
Messages
5,382,120
Members
401,771
Latest member
Polarak

Some videos you may like

This Week's Hot Topics

Top