$15 USD - VBA code to remove rows that sub-total = zero

wsuengineer · Dec 3rd 2017

I have a spread-sheet that reports transactions from multiple vendors and then a balance. Any vendor that has a balance of zero i would like removed from the spreadsheet along with the corresponding rows of transactions. I have a few thousand rows with vendors and balances, but I only need to look at ones with a balance (either positive or negative), so removing vendors without a balance will clean up spreadsheet so it can be evaluated. See the attached example for specifics.

sample data.xlsx

Transaction ID: 4NS81174KL2792718

John_w · Dec 3rd 2017

I can look at this.

John_w · Dec 3rd 2017

The code is ready. I will send my payment details to you and on receipt will post the code here.

Thanks.

wsuengineer · Dec 3rd 2017

John_W,

Done deal. I sent the payment.

John_w · Dec 3rd 2017

Payment received, thanks.

Here is the code. It processes the data on the first sheet in the active workbook, so you must open your data workbook and have it as the active workbook and run the macro.

Code

Public Sub Delete_Rows_With_Zero_Subtotal()


    Dim r As Long
    Dim p1 As Long, p2 As Long
    Dim Hcells As String


    Application.ScreenUpdating = False


    With ActiveWorkbook.Worksheets(1)
        r = .Cells(Rows.Count, "H").End(xlUp).Row
        While r > 0
            p1 = InStr(1, .Cells(r, "H").Formula, "=ROUND(SUM(", vbTextCompare)
            If p1 > 0 And .Cells(r, "H").Value = 0 Then
                p1 = p1 + Len("=ROUND(SUM(")
                p2 = InStr(p1, .Cells(r, "H").Formula, ")")
                Hcells = Mid(.Cells(r, "H").Formula, p1, p2 - p1)
                .Rows(r).EntireRow.Delete
                r = .Range(Hcells).Row - 1
                .Range(Hcells).EntireRow.Delete
            Else
                r = r - 1
            End If
        Wend
    End With


    Application.ScreenUpdating = True


    ActiveWorkbook.Save


    MsgBox "Done"


End Sub

Display More

wsuengineer · Dec 4th 2017

Work complete. The code work well. Thank you.

$15 USD - VBA code to remove rows that sub-total = zero

Participate now!

Share