+ Reply to Thread
Results 1 to 8 of 8

VBA Trouble with Hiding Rows

  1. #1
    Registered User
    Join Date
    04-18-2005
    Posts
    3

    Unhappy VBA Trouble with Hiding Rows

    Good Morning!

    I have been stumped on this problem for awhile and no matter what I use - SOMETHING goes wrong.

    What I am doing is creating an invoice (for various users) to fill out. There are 9 different sections. One section has five subsections, another has four subsections, six sections have one subsection, and the last section does not have a subsection.

    Once the client receives the invoice from a user - they will open two additional sheets - each that looks like the invoice (but not exactly since each sheet performs a breakdown of cost and differences).

    The purpose of this particular VBA is to hide all the unused rows (and/or affiliated rows) when the user has completed filling out the invoice form. I have tried different codings - but again - SOMETHING keeps going wrong.

    The lastest coding that I have is for the first section (with subsections):

    Range (Cells) Z = Total of the line (which is Qty.*Rate)

    'First subsection
    If Range("Z25").Value = 0 Then Rows("25:26").Hidden = True
    If Range("Z27").Value = 0 Then Rows("27:28").Hidden = True
    If Range("Z29").Value = 0 Then Rows("29:30").Hidden = True
    If Range("Z31").Value = 0 Then Rows("31:32").Hidden = True
    If Rows("25:32").Hidden = True Then Rows("24").Hidden = True 'takes out header

    'Second subsection
    If Range("Z35").Value = 0 Then Rows("35:36").Hidden = True
    If Range("Z37").Value = 0 Then Rows("37:38").Hidden = True
    If Range("Z39").Value = 0 Then Rows("39:40").Hidden = True
    If Range("Z41").Value = 0 Then Rows("41:42").Hidden = True
    If Rows("35:42").Hidden = True Then Rows("33:34").Hidden = True 'takes out header and line above

    'Third subsection
    If Range("Z45").Value = 0 Then Rows("45:46").Hidden = True
    If Range("Z47").Value = 0 Then Rows("47:48").Hidden = True
    If Range("Z49").Value = 0 Then Rows("49:50").Hidden = True
    If Range("Z51").Value = 0 Then Rows("51:52").Hidden = True
    If Rows("45:52").Hidden = True Then Rows("43:44").Hidden = True ' takes out header & line above

    'Fourth subsection
    If Range("Z55").Value = 0 Then Rows("55:56").Hidden = True
    If Range("Z57").Value = 0 Then Rows("57:58").Hidden = True
    If Range("Z59").Value = 0 Then Rows("59:60").Hidden = True
    If Range("Z61").Value = 0 Then Rows("61:62").Hidden = True
    If Range("Z63").Value = 0 Then Rows("63:64").Hidden = True
    If Range("Z65").Value = 0 Then Rows("65:66").Hidden = True
    If Range("Z67").Value = 0 Then Rows("67:68").Hidden = True
    If Range("Z69").Value = 0 Then Rows("69:70").Hidden = True
    If Range("Z71").Value = 0 Then Rows("71:72").Hidden = True
    If Range("Z73").Value = 0 Then Rows("73:74").Hidden = True
    If Range("Z75").Value = 0 Then Rows("75:76").Hidden = True
    If Range("Z77").Value = 0 Then Rows("77:78").Hidden = True
    If Range("Z79").Value = 0 Then Rows("79:80").Hidden = True
    If Range("Z81").Value = 0 Then Rows("81:82").Hidden = True
    If Range("Z83").Value = 0 Then Rows("83:84").Hidden = True
    If Range("Z85").Value = 0 Then Rows("85:86").Hidden = True
    If Range("Z87").Value = 0 Then Rows("87:88").Hidden = True
    If Range("Z89").Value = 0 Then Rows("89:90").Hidden = True
    If Rows("55:90").Hidden = True Then Rows("53:54").Hidden = True ' takes out header & line above

    'Fifth subsection
    If Range("Z93").Value = 0 Then Rows("93:94").Hidden = True
    If Range("Z95").Value = 0 Then Rows("95:96").Hidden = True
    If Range("Z97").Value = 0 Then Rows("97:98").Hidden = True
    If Range("Z99").Value = 0 Then Rows("99:100").Hidden = True
    If Range("Z101").Value = 0 Then Rows("101:102").Hidden = True
    If Range("Z103").Value = 0 Then Rows("103:104").Hidden = True
    If Range("Z105").Value = 0 Then Rows("105:106").Hidden = True
    If Rows("93:106").Hidden = True Then Rows("91:92").Hidden = True ' takes out header & line above

    If Rows("24:106").Hidden = True Then Rows("22:113").Hidden = True 'for the whole section



    The end result of this is that it hides the whole section - regardless if there are amounts or not UNLESS the very first row - Range("Z25") has a value > 0.

    This is happening in all the sections.

    What am I doing wrong?

    Your help is VERY much appreciated!

    TIA,
    Sarr
    Last edited by Sarrina; 06-14-2005 at 10:30 AM.

  2. #2
    TomHinkle
    Guest

    RE: VBA Trouble with Hiding Rows

    To be quite honest, I didn't want to trudge through all that code.
    What I would do is create a worksheet (or worksheets) that serve the purpose
    of data entry, then build an invoice/report based on what is keyed in.
    Would be much more straight forward and you wouldn't be collecting tons of
    worksheets that are incredibly different (in regards to hidden rows/columns)

    Hth

    "Sarrina" wrote:

    >
    > Good Morning!
    >
    > I have been stumped on this problem for awhile and no matter what I use
    > - SOMETHING goes wrong.
    >
    > What I am doing is creating an invoice (for various users) to fill out.
    > There are 9 different sections. One section has five subsections,
    > another has four subsections, six sections have one subsection, and the
    > last section does not have a subsection.
    >
    > Once the client receives the invoice from a user - they will open two
    > additional sheets - each that looks like the invoice (but not exactly
    > since each sheet performs a breakdown of cost and differences).
    >
    > The purpose of this particular VBA is to hide all the unused rows
    > (and/or affiliated rows) when the user has completed filling out the
    > invoice form. I have tried different codings - but again - SOMETHING
    > keeps going wrong.
    >
    > The lastest coding that I have is for the first section (with
    > subsections):
    >
    > Range (Cells) Z = Total of the line (which is Qty.*Rate)
    >
    > 'First subsection
    > If Range("Z25").Value = 0 Then Rows("25:26").Hidden = True
    > If Range("Z27").Value = 0 Then Rows("27:28").Hidden = True
    > If Range("Z29").Value = 0 Then Rows("29:30").Hidden = True
    > If Range("Z31").Value = 0 Then Rows("31:32").Hidden = True
    > If Rows("25:32").Hidden = True Then Rows("24").Hidden =
    > True 'takes out header
    >
    > 'Second subsection
    > If Range("Z35").Value = 0 Then Rows("35:36").Hidden = True
    > If Range("Z37").Value = 0 Then Rows("37:38").Hidden = True
    > If Range("Z39").Value = 0 Then Rows("39:40").Hidden = True
    > If Range("Z41").Value = 0 Then Rows("41:42").Hidden = True
    > If Rows("35:42").Hidden = True Then Rows("33:34").Hidden = True
    > 'takes out header and line above
    >
    > 'Third subsection
    > If Range("Z45").Value = 0 Then Rows("45:46").Hidden = True
    > If Range("Z47").Value = 0 Then Rows("47:48").Hidden = True
    > If Range("Z49").Value = 0 Then Rows("49:50").Hidden = True
    > If Range("Z51").Value = 0 Then Rows("51:52").Hidden = True
    > If Rows("45:52").Hidden = True Then Rows("43:44").Hidden =
    > True ' takes out header & line above
    >
    > 'Fourth subsection
    > If Range("Z55").Value = 0 Then Rows("55:56").Hidden = True
    > If Range("Z57").Value = 0 Then Rows("57:58").Hidden = True
    > If Range("Z59").Value = 0 Then Rows("59:60").Hidden = True
    > If Range("Z61").Value = 0 Then Rows("61:62").Hidden = True
    > If Range("Z63").Value = 0 Then Rows("63:64").Hidden = True
    > If Range("Z65").Value = 0 Then Rows("65:66").Hidden = True
    > If Range("Z67").Value = 0 Then Rows("67:68").Hidden = True
    > If Range("Z69").Value = 0 Then Rows("69:70").Hidden = True
    > If Range("Z71").Value = 0 Then Rows("71:72").Hidden = True
    > If Range("Z73").Value = 0 Then Rows("73:74").Hidden = True
    > If Range("Z75").Value = 0 Then Rows("75:76").Hidden = True
    > If Range("Z77").Value = 0 Then Rows("77:78").Hidden = True
    > If Range("Z79").Value = 0 Then Rows("79:80").Hidden = True
    > If Range("Z81").Value = 0 Then Rows("81:82").Hidden = True
    > If Range("Z83").Value = 0 Then Rows("83:84").Hidden = True
    > If Range("Z85").Value = 0 Then Rows("85:86").Hidden = True
    > If Range("Z87").Value = 0 Then Rows("87:88").Hidden = True
    > If Range("Z89").Value = 0 Then Rows("89:90").Hidden = True
    > If Rows("55:90").Hidden = True Then Rows("53:54").Hidden = True
    > ' takes out header & line above
    >
    > 'Fifth subsection
    > If Range("Z93").Value = 0 Then Rows("93:94").Hidden = True
    > If Range("Z95").Value = 0 Then Rows("95:96").Hidden = True
    > If Range("Z97").Value = 0 Then Rows("97:98").Hidden = True
    > If Range("Z99").Value = 0 Then Rows("99:100").Hidden = True
    > If Range("Z101").Value = 0 Then Rows("101:102").Hidden = True
    > If Range("Z103").Value = 0 Then Rows("103:104").Hidden = True
    > If Range("Z105").Value = 0 Then Rows("105:106").Hidden = True
    > If Rows("93:106").Hidden = True Then Rows("91:92").Hidden =
    > True ' takes out header & line above
    >
    > If Rows("24:106").Hidden = True Then
    > Rows("22:113").Hidden = True 'for the whole section
    >
    >
    > The end result of this is that it hides the whole section - regardless
    > if there are amounts or not UNLESS the very first row - Range("Z25")
    > has a value > 0.
    >
    > This is happening in all the sections.
    >
    > What am I doing wrong?
    >
    > Your help is VERY much appreciated!
    >
    > TIA,
    > Sarr
    >
    >
    > --
    > Sarrina
    > ------------------------------------------------------------------------
    > Sarrina's Profile: http://www.excelforum.com/member.php...o&userid=22337
    > View this thread: http://www.excelforum.com/showthread...hreadid=379002
    >
    >


  3. #3
    Registered User
    Join Date
    04-18-2005
    Posts
    3
    The coding is basically the same thing (just different rows).

    What I was trying to get was why it just seems to read the very first line of each subsection and hides the following rows based off of that line.


    Your idea for entering data into another worksheet will not work. And the assumption of "different" looking invoices is wrong. It's all the exact same appearance.

    Column "Z" is the line totals (also the column that carries the subtotals and Total)

    There are validations, formulas, and dropdown lists from Columns A thru Y. In addition - the worksheet that users use are tied into two hidden worksheets involving more formulas, IFs, etc.

    Thanks,
    Sarr

    P.S. Coding is a lot longer - the first post was just the first section so that the reader of this will know what I'm writing.

  4. #4
    Forum Contributor
    Join Date
    01-21-2005
    Location
    Colorado
    MS-Off Ver
    2000,2003,2007
    Posts
    481
    I did just a quick test on the code for the first subsection and found that if you have only row 25 hidden but not any of the other 26-32 it will hide row 24.

    Apparently this line only considers the first argument when checking for hidden = true

    If Rows("25:32").Hidden = True Then Rows("24").Hidden = True

    That may give you a starting place

  5. #5
    Forum Contributor
    Join Date
    01-21-2005
    Location
    Colorado
    MS-Off Ver
    2000,2003,2007
    Posts
    481
    You can check the hidden status of each row using an If AND type of statement.

    Replace:

    If Rows("25:32").Hidden = True Then Rows("24").Hidden = True

    With:

    If Rows("25").Hidden = True And Rows("27").Hidden = True And Rows("29").Hidden = True And Rows("31").Hidden = True Then Rows("24").Hidden = True 'takes out header

    This will obviously give you a very long line of code for your fourth subsection but is a viable workaround unless there is a more concise way to achieve the desired results.
    To hide all rows you can just check if the header row from each section has been hidden with your last line of code instead of each line.

    You may be able to use a Select Case statement as well.

    HTH

  6. #6
    Registered User
    Join Date
    04-18-2005
    Posts
    3
    Thanks! Your input may just help...I'll give it a shot this afternoon.


    Thanks!
    Sarr

  7. #7
    JMB
    Guest

    RE: VBA Trouble with Hiding Rows

    You could set up a function to test your ranges to see if all of the rows are
    hidden

    Sub test()
    If AllRowsHidden(Rows("1:5")) Then
    MsgBox "all hidden"
    Else: MsgBox "some visible"
    End If

    End Sub


    Function AllRowsHidden(Rng As Range) As Boolean
    AllRowsHidden = True
    For Each x In Rng
    If Not x.Hidden Then
    AllRowsHidden = False
    Exit Function
    End If
    Next x
    End Function


    "Sarrina" wrote:

    >
    > Good Morning!
    >
    > I have been stumped on this problem for awhile and no matter what I use
    > - SOMETHING goes wrong.
    >
    > What I am doing is creating an invoice (for various users) to fill out.
    > There are 9 different sections. One section has five subsections,
    > another has four subsections, six sections have one subsection, and the
    > last section does not have a subsection.
    >
    > Once the client receives the invoice from a user - they will open two
    > additional sheets - each that looks like the invoice (but not exactly
    > since each sheet performs a breakdown of cost and differences).
    >
    > The purpose of this particular VBA is to hide all the unused rows
    > (and/or affiliated rows) when the user has completed filling out the
    > invoice form. I have tried different codings - but again - SOMETHING
    > keeps going wrong.
    >
    > The lastest coding that I have is for the first section (with
    > subsections):
    >
    > Range (Cells) Z = Total of the line (which is Qty.*Rate)
    >
    > 'First subsection
    > If Range("Z25").Value = 0 Then Rows("25:26").Hidden = True
    > If Range("Z27").Value = 0 Then Rows("27:28").Hidden = True
    > If Range("Z29").Value = 0 Then Rows("29:30").Hidden = True
    > If Range("Z31").Value = 0 Then Rows("31:32").Hidden = True
    > If Rows("25:32").Hidden = True Then Rows("24").Hidden =
    > True 'takes out header
    >
    > 'Second subsection
    > If Range("Z35").Value = 0 Then Rows("35:36").Hidden = True
    > If Range("Z37").Value = 0 Then Rows("37:38").Hidden = True
    > If Range("Z39").Value = 0 Then Rows("39:40").Hidden = True
    > If Range("Z41").Value = 0 Then Rows("41:42").Hidden = True
    > If Rows("35:42").Hidden = True Then Rows("33:34").Hidden = True
    > 'takes out header and line above
    >
    > 'Third subsection
    > If Range("Z45").Value = 0 Then Rows("45:46").Hidden = True
    > If Range("Z47").Value = 0 Then Rows("47:48").Hidden = True
    > If Range("Z49").Value = 0 Then Rows("49:50").Hidden = True
    > If Range("Z51").Value = 0 Then Rows("51:52").Hidden = True
    > If Rows("45:52").Hidden = True Then Rows("43:44").Hidden =
    > True ' takes out header & line above
    >
    > 'Fourth subsection
    > If Range("Z55").Value = 0 Then Rows("55:56").Hidden = True
    > If Range("Z57").Value = 0 Then Rows("57:58").Hidden = True
    > If Range("Z59").Value = 0 Then Rows("59:60").Hidden = True
    > If Range("Z61").Value = 0 Then Rows("61:62").Hidden = True
    > If Range("Z63").Value = 0 Then Rows("63:64").Hidden = True
    > If Range("Z65").Value = 0 Then Rows("65:66").Hidden = True
    > If Range("Z67").Value = 0 Then Rows("67:68").Hidden = True
    > If Range("Z69").Value = 0 Then Rows("69:70").Hidden = True
    > If Range("Z71").Value = 0 Then Rows("71:72").Hidden = True
    > If Range("Z73").Value = 0 Then Rows("73:74").Hidden = True
    > If Range("Z75").Value = 0 Then Rows("75:76").Hidden = True
    > If Range("Z77").Value = 0 Then Rows("77:78").Hidden = True
    > If Range("Z79").Value = 0 Then Rows("79:80").Hidden = True
    > If Range("Z81").Value = 0 Then Rows("81:82").Hidden = True
    > If Range("Z83").Value = 0 Then Rows("83:84").Hidden = True
    > If Range("Z85").Value = 0 Then Rows("85:86").Hidden = True
    > If Range("Z87").Value = 0 Then Rows("87:88").Hidden = True
    > If Range("Z89").Value = 0 Then Rows("89:90").Hidden = True
    > If Rows("55:90").Hidden = True Then Rows("53:54").Hidden = True
    > ' takes out header & line above
    >
    > 'Fifth subsection
    > If Range("Z93").Value = 0 Then Rows("93:94").Hidden = True
    > If Range("Z95").Value = 0 Then Rows("95:96").Hidden = True
    > If Range("Z97").Value = 0 Then Rows("97:98").Hidden = True
    > If Range("Z99").Value = 0 Then Rows("99:100").Hidden = True
    > If Range("Z101").Value = 0 Then Rows("101:102").Hidden = True
    > If Range("Z103").Value = 0 Then Rows("103:104").Hidden = True
    > If Range("Z105").Value = 0 Then Rows("105:106").Hidden = True
    > If Rows("93:106").Hidden = True Then Rows("91:92").Hidden =
    > True ' takes out header & line above
    >
    > If Rows("24:106").Hidden = True Then
    > Rows("22:113").Hidden = True 'for the whole section
    >
    >
    > The end result of this is that it hides the whole section - regardless
    > if there are amounts or not UNLESS the very first row - Range("Z25")
    > has a value > 0.
    >
    > This is happening in all the sections.
    >
    > What am I doing wrong?
    >
    > Your help is VERY much appreciated!
    >
    > TIA,
    > Sarr
    >
    >
    > --
    > Sarrina
    > ------------------------------------------------------------------------
    > Sarrina's Profile: http://www.excelforum.com/member.php...o&userid=22337
    > View this thread: http://www.excelforum.com/showthread...hreadid=379002
    >
    >


  8. #8
    keepITcool
    Guest

    Re: VBA Trouble with Hiding Rows


    would it not make more sense to UNHIDE
    if they have a non zero value?

    Rows("25:26").Hidden = (Range("Z25").Value = 0)

    to shorten your code..
    For each rCell in Range("Z25,Z27,Z29")
    rcell.resize(2).entirerow.hidden = (rcell.value=0)
    next


    --
    keepITcool
    | www.XLsupport.com | keepITcool chello nl | amsterdam


    Sarrina wrote :

    >
    > Good Morning!
    >
    > I have been stumped on this problem for awhile and no matter what I
    > use - SOMETHING goes wrong.
    >
    > What I am doing is creating an invoice (for various users) to fill
    > out. There are 9 different sections. One section has five
    > subsections, another has four subsections, six sections have one
    > subsection, and the last section does not have a subsection.
    >
    > Once the client receives the invoice from a user - they will open two
    > additional sheets - each that looks like the invoice (but not exactly
    > since each sheet performs a breakdown of cost and differences).
    >
    > The purpose of this particular VBA is to hide all the unused rows
    > (and/or affiliated rows) when the user has completed filling out the
    > invoice form. I have tried different codings - but again - SOMETHING
    > keeps going wrong.
    >
    > The lastest coding that I have is for the first section (with
    > subsections):
    >
    > Range (Cells) Z = Total of the line (which is Qty.*Rate)
    >
    > 'First subsection
    > If Range("Z25").Value = 0 Then Rows("25:26").Hidden = True
    > If Range("Z27").Value = 0 Then Rows("27:28").Hidden = True
    > If Range("Z29").Value = 0 Then Rows("29:30").Hidden = True
    > If Range("Z31").Value = 0 Then Rows("31:32").Hidden = True
    > If Rows("25:32").Hidden = True Then Rows("24").Hidden =
    > True 'takes out header
    >
    > 'Second subsection
    > If Range("Z35").Value = 0 Then Rows("35:36").Hidden = True
    > If Range("Z37").Value = 0 Then Rows("37:38").Hidden = True
    > If Range("Z39").Value = 0 Then Rows("39:40").Hidden = True
    > If Range("Z41").Value = 0 Then Rows("41:42").Hidden = True
    > If Rows("35:42").Hidden = True Then Rows("33:34").Hidden = True
    > 'takes out header and line above
    >
    > 'Third subsection
    > If Range("Z45").Value = 0 Then Rows("45:46").Hidden = True
    > If Range("Z47").Value = 0 Then Rows("47:48").Hidden = True
    > If Range("Z49").Value = 0 Then Rows("49:50").Hidden = True
    > If Range("Z51").Value = 0 Then Rows("51:52").Hidden = True
    > If Rows("45:52").Hidden = True Then Rows("43:44").Hidden =
    > True ' takes out header & line above
    >
    > 'Fourth subsection
    > If Range("Z55").Value = 0 Then Rows("55:56").Hidden = True
    > If Range("Z57").Value = 0 Then Rows("57:58").Hidden = True
    > If Range("Z59").Value = 0 Then Rows("59:60").Hidden = True
    > If Range("Z61").Value = 0 Then Rows("61:62").Hidden = True
    > If Range("Z63").Value = 0 Then Rows("63:64").Hidden = True
    > If Range("Z65").Value = 0 Then Rows("65:66").Hidden = True
    > If Range("Z67").Value = 0 Then Rows("67:68").Hidden = True
    > If Range("Z69").Value = 0 Then Rows("69:70").Hidden = True
    > If Range("Z71").Value = 0 Then Rows("71:72").Hidden = True
    > If Range("Z73").Value = 0 Then Rows("73:74").Hidden = True
    > If Range("Z75").Value = 0 Then Rows("75:76").Hidden = True
    > If Range("Z77").Value = 0 Then Rows("77:78").Hidden = True
    > If Range("Z79").Value = 0 Then Rows("79:80").Hidden = True
    > If Range("Z81").Value = 0 Then Rows("81:82").Hidden = True
    > If Range("Z83").Value = 0 Then Rows("83:84").Hidden = True
    > If Range("Z85").Value = 0 Then Rows("85:86").Hidden = True
    > If Range("Z87").Value = 0 Then Rows("87:88").Hidden = True
    > If Range("Z89").Value = 0 Then Rows("89:90").Hidden = True
    > If Rows("55:90").Hidden = True Then Rows("53:54").Hidden = True
    > ' takes out header & line above
    >
    > 'Fifth subsection
    > If Range("Z93").Value = 0 Then Rows("93:94").Hidden = True
    > If Range("Z95").Value = 0 Then Rows("95:96").Hidden = True
    > If Range("Z97").Value = 0 Then Rows("97:98").Hidden = True
    > If Range("Z99").Value = 0 Then Rows("99:100").Hidden = True
    > If Range("Z101").Value = 0 Then Rows("101:102").Hidden = True
    > If Range("Z103").Value = 0 Then Rows("103:104").Hidden = True
    > If Range("Z105").Value = 0 Then Rows("105:106").Hidden = True
    > If Rows("93:106").Hidden = True Then Rows("91:92").Hidden =
    > True ' takes out header & line above
    >
    > If Rows("24:106").Hidden = True Then
    > Rows("22:113").Hidden = True 'for the whole section
    >
    >
    > The end result of this is that it hides the whole section - regardless
    > if there are amounts or not UNLESS the very first row - Range("Z25")
    > has a value > 0.
    >
    > This is happening in all the sections.
    >
    > What am I doing wrong?
    >
    > Your help is VERY much appreciated!
    >
    > TIA,
    > Sarr


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1