delete row if sum is zero based on a different column

[pook_666]

Hi magicians,

I have the below simplified table of what I'm working on.

I want to run a VBA to to delete rows where the sum of column B is zero for the same number in column A. E.g. for 1001 in column A, the sum of column B is zero, so these rows should be deleted and so forth leaving just two rows of 1003 for 100 each.

The current list I am working on has 50k+ lines, hence why a VBA would be needed.

Any ideas for a starting point would be greatly appreciated!

 

[Marc L]

Hi, for a starting point share a worksheet via this forum tool XL2BB or better a workbook link on a files host website like Dropbox …​

 

[Marc L]

According to your small example a VBA demonstration for starters :​

Sub Demo1()
    Dim R&, V, F&, S@
        R = 1
        Application.ScreenUpdating = False
    With [A1].CurrentRegion.Rows
        V = .Resize(.Count + 1).Value2
    While R < .Count
        R = R + 1:  F = R:  S = V(R, 2)
        While V(R + 1, 1) = V(F, 1):  R = R + 1:  S = S + V(R, 2):  Wend
        If S = 0 Then .Item(F & ":" & R).Clear
    Wend
       .Sort .Cells(1), 1, Header:=1
    End With
        Application.ScreenUpdating = True
End Sub

 

[Marc L]

Another starters but limited to 65k rows :​

Sub Demo2()
        Dim A, B, R&, L&, S@
    With [A1].CurrentRegion.Columns
        A = Application.Transpose(.Item(1))
        B = Application.Transpose(.Item(2))
        R = .Rows.Count
    While R > 2
        L = R:  S = B(R)
        While A(R - 1) = A(L):  R = R - 1:  S = S + B(R):  Wend
        If S Then R = R - 1 Else For R = L To R Step -1: A(R) = False: B(R) = False: Next
    Wend
        A = Filter(A, False, False):  B = Filter(B, False, False)
       .Clear
       .Resize(UBound(A) + 1).Value2 = Application.Transpose(Array(A, B))
    End With
End Sub

 

[pook_666]

Thank you Marc for your reply.

Can you advise how this works please as I want to see how I can integrate this into my current spreadsheet where instead of columns A & B having the required data, it is actually columns B & J where J have the numbers and B is the column A in the above scenario.

Do I just change references to A and B in your VBA to B & J respectively?

Thanks once again for your help.

 

[Marc L]

If the worksheet design is smart enough you can change the index column # …​

 

[pook_666]

Awesome - thanks Marc this seems to have worked