VBA Trouble with Hiding Rows

[Sarrina]

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

 

[Guest]

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]

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.

 

[bhofsetz]

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

 

[bhofsetz]

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

 

[Sarrina]

Thanks! Your input may just help...I'll give it a shot this afternoon.


Thanks!
Sar

 

[Guest]

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

 

[keepITcool]

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 :