Macro to Delete values summing to zero where A/c is the same

[howard]

I have values in Col D and a/c numbers in Col A

Where the values sum to zero for the same account number for e.g if the a/c 1 is 1 , and the values for 1 sum to zero then all the rows for 1 to be deleted etc

I have highlighted those rose that need to be deleted

It would be appreciated if someone can provide me with code to do so


Se link below

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[mumps]

Try:

Sub MyDeleteMacro()
    Application.ScreenUpdating = False
    Dim i As Long, v As Variant, rng As Range, total As Long
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    Set rng = Range("D2", Range("D" & Rows.Count).End(xlUp))
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v, 1)
            If Not .Exists(v(i, 1)) Then
                .Add v(i, 1), Nothing
                With Range("A1")
                    .CurrentRegion.AutoFilter 1, v(i, 1)
                    total = Application.WorksheetFunction.Sum(rng.SpecialCells(xlCellTypeVisible))
                    If total = 0 Then
                        ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
                    End If
                End With
            End If
        Next i
    End With
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[howard]

Thanks Mumps. This works perfectly

 

[mumps]

You are very welcome.