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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. John

    Hide rows if formula result is zero

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

    Hide rows when quantity is zero

    Michael, Oct 5, 2004, in forum: Excel Programming
    Replies:
    8
    Views:
    128
    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:
    169
    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:
    121
    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:
    97
    Ron de Bruin
    Sep 28, 2005
  7. Todd F.
    Replies:
    5
    Views:
    155
    Todd F.
    Mar 13, 2006
  8. Thulasiram
    Replies:
    4
    Views:
    122
    Thulasiram
    Sep 26, 2006
Loading...