| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- Sarrina ------------------------------------------------------------------------ Sarrina's Profile: http://www.excelforum.com/member.php...o&userid=22337 View this thread: http://www.excelforum.com/showthread...hreadid=379002 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Trouble with Hiding Rows
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. -- Sarrina ------------------------------------------------------------------------ Sarrina's Profile: http://www.excelforum.com/member.php...o&userid=22337 View this thread: http://www.excelforum.com/showthread...hreadid=379002 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Trouble with Hiding Rows
I did just a quick test on the code for the first subsection and foun that if you have only row 25 hidden but not any of the other 26-32 i will hide row 24. Apparently this line only considers the first argument when checkin for hidden = true If Rows("25:32").Hidden = True Then Rows("24").Hidden = True That may give you a starting plac -- bhofset ----------------------------------------------------------------------- bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880 View this thread: http://www.excelforum.com/showthread.php?threadid=37900 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Trouble with Hiding Rows
You can check the hidden status of each row using an If AND type o statement. Replace: If Rows("25:32").Hidden = True Then Rows("24").Hidden = True With: If Rows("25").Hidden = True And Rows("27").Hidden = True An Rows("29").Hidden = True And Rows("31").Hidden = True The Rows("24").Hidden = True 'takes out header This will obviously give you a very long line of code for your fourt subsection but is a viable workaround unless there is a more concis way to achieve the desired results. To hide all rows you can just check if the header row from each sectio 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. HT -- bhofset ----------------------------------------------------------------------- bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880 View this thread: http://www.excelforum.com/showthread.php?threadid=37900 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Trouble with Hiding Rows
Thanks! Your input may just help...I'll give it a shot this afternoon. Thanks! Sar -- Sarrin ----------------------------------------------------------------------- Sarrina's Profile: http://www.excelforum.com/member.php...fo&userid=2233 View this thread: http://www.excelforum.com/showthread.php?threadid=37900 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Color alternate rows when after hiding selected rows | Excel Worksheet Functions | |||
| Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
| Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
| Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
| Trouble hiding commandbars | Excel Programming | |||
Linear Mode
