Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Blacktown, Sydney, New South Wales, Australia
    Thanked 0 Times in 0 Posts

    Eliminate Zeros (XL XP)

    As part of Month End , I am required to reconcile the Goods Receipts and Invoice Clearing account. I get a data download from the Corporate Accounting system in excel format. There are thousands of entries. I need to be able to eliminate from the file all entries for a Purchase Order which add to zero. In other words, The Invoice Value less the receipts nets to zero. There is usually one invoice value, but there may be many receipts.
    The approach so far has been to sort the data and use the SubTotal function to identify the zero values. I the highlight each line adding to zero and delete the rows. What is left are the transactions that make up the balance.
    This task takes hours, As I need to highlight each set of rows individually. I have tried to sort the Sub total lines, but then lose the Sub Totals. I have also tried Filtering but this does not work either.

    There has to be a function or VBA snippet that will save me the time. (I have attached a file with sample data)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Eliminate Zeros (XL XP)

    Try this macro - make sure to test it on a copy of the real data first!

    Sub RemoveZeroBalance()
    Dim lngMaxRow As Long
    Dim lngRow As Long
    Dim lngCurRow As Long
    Dim dblBalance As Double
    Range("A1").Sort Key1:=Range("A1"), Header:=xlYes
    lngMaxRow = Range("A65536").End(xlUp).Row
    lngRow = lngMaxRow
    Do While lngRow > 1
    dblBalance = 0
    lngCurRow = lngRow
    Do While Range("A" & lngCurRow) = Range("A" & lngRow)
    dblBalance = dblBalance + Range("B" & lngCurRow)
    lngCurRow = lngCurRow - 1
    If dblBalance = 0 Then
    Range((lngCurRow + 1) & ":" & lngRow).Delete
    End If
    lngRow = lngCurRow
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Eliminate Zeros (XL XP)

    An alternative to a macro is to use a sumif and a filter:
    Start witht he unfiltered/ non-subtotaled data

    In C1 enter a header (eg Total)
    In C2:

    Copy c2 to c3:C24
    Now data-filter-autofilter on column c for zero (0), select these rows and delete...

    This methods does not require sorting the data


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts