hide rows with zero balance

Discussion in 'Excel Programming' started by andresg1975, Jul 23, 2007.

  1. andresg1975

    andresg1975 Guest

    i have, lets say
    a b c d
    1 1 4 5 9
    2 8 0 8 7
    3 0 0 0 0
    4 7 8 5 9
    5 0 0 0 0
    6 5 8 7 4

    how can i hide rows where column a,b,c and d have zero balances
    In this case, hiding row 3 and 5
    Any help would be greatly appreciated
     
    andresg1975, Jul 23, 2007
    #1
    1. Advertisements

  2. andresg1975

    Guest

    On 23 Jul, 17:24, andresg1975 <>
    wrote:
    > i have, lets say
    > a b c d
    > 1 1 4 5 9
    > 2 8 0 8 7
    > 3 0 0 0 0
    > 4 7 8 5 9
    > 5 0 0 0 0
    > 6 5 8 7 4
    >
    > how can i hide rows where column a,b,c and d have zero balances
    > In this case, hiding row 3 and 5
    > Any help would be greatly appreciated



    Try this for where your data is in cells B1 to E7:


    Dim rngHide As Range

    For Each rngHide In Range("A2:A7")
    If WorksheetFunction.Sum(rngHide.Offset(0, 1).Resize(1, 5)) = 0
    Then
    rngHide.EntireRow.Hidden = True
    End If
    Next rngHide



    Regards,

    Toyin
     
    , Jul 23, 2007
    #2
    1. Advertisements

  3. andresg1975

    andresg1975 Guest

    It didn't work due to a syntax error in here:
    If WorksheetFunction.Sum(rngHide.Offset(0, 1).Resize(1, 5)) = 0


    "" wrote:

    > On 23 Jul, 17:24, andresg1975 <>
    > wrote:
    > > i have, lets say
    > > a b c d
    > > 1 1 4 5 9
    > > 2 8 0 8 7
    > > 3 0 0 0 0
    > > 4 7 8 5 9
    > > 5 0 0 0 0
    > > 6 5 8 7 4
    > >
    > > how can i hide rows where column a,b,c and d have zero balances
    > > In this case, hiding row 3 and 5
    > > Any help would be greatly appreciated

    >
    >
    > Try this for where your data is in cells B1 to E7:
    >
    >
    > Dim rngHide As Range
    >
    > For Each rngHide In Range("A2:A7")
    > If WorksheetFunction.Sum(rngHide.Offset(0, 1).Resize(1, 5)) = 0
    > Then
    > rngHide.EntireRow.Hidden = True
    > End If
    > Next rngHide
    >
    >
    >
    > Regards,
    >
    > Toyin
    >
    >
     
    andresg1975, Jul 23, 2007
    #3
  4. andresg1975

    Guest

    The syntax is due to you not having "Then" after the 0

    For some reason the text has wrapped to the next line in my reply.

    Let me know,

    Toyin.
     
    , Jul 24, 2007
    #4
  5. andresg1975

    andresg1975 Guest

    Thanks a lot for your help!

    "" wrote:

    > The syntax is due to you not having "Then" after the 0
    >
    > For some reason the text has wrapped to the next line in my reply.
    >
    > Let me know,
    >
    > Toyin.
    >
    >
     
    andresg1975, Jul 25, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John

    Hide rows if formula result is zero

    John, Oct 30, 2003, in forum: Excel Programming
    Replies:
    3
    Views:
    61
    Randy
    Oct 30, 2003
  2. Peter
    Replies:
    2
    Views:
    78
    Norman Jones
    Jul 29, 2004
  3. Michael

    Hide rows when quantity is zero

    Michael, Oct 5, 2004, in forum: Excel Programming
    Replies:
    8
    Views:
    87
    Michael
    Oct 6, 2004
  4. Knowledge Networks

    How can I hide zero rows in a pivot table with calculated items?

    Knowledge Networks, Oct 23, 2004, in forum: Excel Programming
    Replies:
    0
    Views:
    106
    Knowledge Networks
    Oct 23, 2004
  5. Vlookup help

    Conditional Hide rows with zero

    Vlookup help, Nov 11, 2004, in forum: Excel Programming
    Replies:
    2
    Views:
    101
    kdreyer
    Feb 4, 2005
  6. natrujillo12

    Can you create a macro to hide rows with zero values?

    natrujillo12, Sep 28, 2005, in forum: Excel Programming
    Replies:
    1
    Views:
    71
    Ron de Bruin
    Sep 28, 2005
  7. Todd F.
    Replies:
    5
    Views:
    111
    Todd F.
    Mar 13, 2006
  8. Thulasiram
    Replies:
    4
    Views:
    97
    Thulasiram
    Sep 26, 2006
Loading...