Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filter for non zero calc over rows (Excel 2000>)

    Hi all....long time no see!!!

    Please help with this filter:

    Column E refers to the product code, the sum of the quantities in column F should equals zero,
    Column H refers to their reference number which is both the same for the positive and negative quantity.
    The ones in yellow-just for this purpose , the sum equals zero. (The problem is that the detail in yellow -i do not want to see,)

    I want to eliminate the one's in column F for which the sum of the negative & positive qty's equals zero.
    I just want to see the one's in column F which do not equal zero, with their corresponding reference number in column H.

    Tx (See attached)
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter for non zero calc over rows (Excel 2000>)

    Try this macro:

    Sub SpecialFilter()
    Dim lngRow As Long
    Dim lngCnt As Long
    Dim dblSum As Double
    Dim lngMaxRow As Long
    Range("A1").CurrentRegion.Sort Key1:="TPROD", _
    Key2:="TREF", Header:=xlYes
    lngMaxRow = Range("A65536").End(xlUp).Row
    lngRow = 2
    Do While lngRow < lngMaxRow
    lngCnt = 1
    dblSum = Cells(lngRow, 6)
    Do While Cells(lngRow + lngCnt, 5) = Cells(lngRow, 5) And _
    Cells(lngRow + lngCnt, 8) = Cells(lngRow, 8)
    dblSum = dblSum + Cells(lngRow + lngCnt, 6)
    lngCnt = lngCnt + 1
    Loop
    If dblSum = 0 Then
    Cells(lngRow, 1).Resize(lngCnt, 1).EntireRow.Hidden = True
    End If
    lngRow = lngRow + lngCnt
    Loop
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter for non zero calc over rows (Excel 2000>)

    Hans, I tested it in the actual file...and it works supurb

    Many tx
    Awesome!!!
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter for non zero calc over rows (Excel 2000>)

    > It works supurb

    Does that mean that it won't work downtown? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter for non zero calc over rows (Excel 2000>)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Superb!
    Regards,
    Rudi

Posting Permissions

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