delete data if the two columns contain zero together

[Hasson]

Hello
I have about 7000 rows . I search for way delete data contin zero or empty value for columns D,E together then should delete it.

orginal

DELETE2.xlsm
	A	B	C	D	E	F
1	ITEM	ID	QTY1	QTY2	QTY3	BALANCE
2	1	TT/W-1 MM CLA1 23M-1 IT	500			500
3	2	QQW-2 TH NM-1 CLA2 VBG L CHI				0
4	3	QQW-3 CV CLA3 TAI	700	33		733
5	4	QQW-4 M*12.5 CLA4 TR	200		12	188
6	5	QQW-5 CLA5 EG	300			300
7	6	MMR12/100 AS-1000/1 TMR	12	0	0	12
8	7	QQW-6 M230 TU	1			1
9	8	QQW-7 S** CLA7 US	1400		1	1399
10	9	QQW-8 CLA8 UK				0
11	10	QQW-9 CLA9 N BR	1600		1	1599
12	11	QQW-10 BN CLA10 IT	800	0	0	800
13	12	QQW-11 LVD CH	900			900
14	13	BB12 QQW-12 CLA12 JA	1000		11	989
sheet1

Cell Formulas
Range	Formula
F2:F14	F2	=C2+D2-E2

result

DELETE2.xlsm
	A	B	C	D	E	F
1	ITEM	ID	QTY1	QTY2	QTY3	BALANCE
2	1	QQW-3 CV CLA3 TAI	700	33		733
3	2	QQW-4 M*12.5 CLA4 TR	200		12	188
4	3	QQW-7 S** CLA7 US	1400		1	1399
5	4	QQW-9 CLA9 N BR	1600		1	1599
6	5	BB12 QQW-12 CLA12 JA	1000		11	989
sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=C2+D2-E2

 

[Hasson]

@bebo021999 many thanks

 

[Hasson]

@kevin9999 your solution are fantastic !
much appreciated for that .

 

[kevin9999]

@kevin9999 your solution are fantastic !
much appreciated for that .

Glad we could help Hasson, and thanks for the feedback
Although you've marked another post as a solution, I still needed to tidy up/correct a couple of things in the code I provided. For reference only:

Option Explicit
Sub Hasson_V3()
    Application.ScreenUpdating = False
    Dim LRow As Long, LCol As Long, i As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Dim a, b
    
    ws2.UsedRange.Clear
    With ws1.Range("A1").CurrentRegion
        .Copy
        ws2.Range("A1").PasteSpecial xlPasteColumnWidths
        ws2.Range("A1").PasteSpecial xlPasteAll
        Application.CutCopyMode = False
    End With
    
    LRow = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row
    LCol = ws2.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1
    
    a = ws2.Range("D2:E" & LRow)
    ReDim b(1 To UBound(a, 1), 1 To 1)
    
    For i = 1 To UBound(a)
        If a(i, 1) + a(i, 2) = 0 Then b(i, 1) = 1
    Next i
    
    ws2.Cells(2, LCol).Resize(UBound(a)) = b
    i = WorksheetFunction.Sum(Columns(LCol))
    ws2.Range(ws2.Cells(2, 1), ws2.Cells(LRow, LCol)).Sort Key1:=ws2.Cells(2, LCol), order1:=1, Header:=2
    If i > 0 Then ws2.Cells(2, LCol).Resize(i).EntireRow.Delete
    
    ws2.Columns(LCol).ClearContents
    LRow = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row
    With ws2.Range("A2:A" & LRow)
        .Formula = "=Row()-1"
        .Value = .Value
    End With
    
    Application.ScreenUpdating = True
End Sub

 

[Hasson]

thanks again .

Although you've marked another post as a solution

actually that's becuase no loop and this is very fast when run the macro then , but today happens strange thing it's too slow to delete it when I want to test which code is speed to mark solution . I no know why does it now , but your code works nicely
just question why you declare some variable after set sheets ?
actually I used to declare before any setting for object .

I still needed to tidy up/correct a couple of things in the code I provided. For reference only

could old versions codes cause problem in the future?

 

[kevin9999]

thanks again .

actually that's becuase no loop and this is very fast when run the macro then , but today happens strange thing it's too slow to delete it when I want to test which code is speed to mark solution . I no know why does it now , but your code works nicely
just question why you declare some variable after set sheets ?
actually I used to declare before any setting for object .

could old versions codes cause problem in the future?

No reason in particular, as long as you declare variables before you 'set' that variable. One school of thought is to declare variables just before you use them.
Although my code does 'loop', because it happens within an array it happens very quickly in RAM.
I would discard my first code - it doubled up the copy unnecessarily.
Thanks for the feedback.