Code for Excel 2007 to hide rows based on sum of several rows not ina range

[Joe Gardill]

I have a financial summary and depending on the selections some rows are
always going to have $0 values.

I need VBA code that will automatically run each time the worksheet
recalculates that will hide a row with all $0 values in columns B through F.
It affects a total of five rows and they all operate on different
conditions, but the rule would apply to all of them if the sum of columns B
through F for the row = $0.

Any help would be appreciated. I am only starting to work with code and have
never taken any courses so if you would not mind I would get a lot more out
of it with a simple explanation of how it works or a reference to review it.

Thank you in advance.

Joe
Frustrated in Baltimore

 

[SixSigmaGuy]

First of all, you mentioned that the sum of the numbers add up to zero. Does that mean we can assume the numbers are never negative? Although, I guess it wouldn't make much different on the coding.
Secondly, do we know what rows to check? You say there are only 5 rows we care about, but are they always in the same place? If not, we need to know more about the data so we can tell what rows to check. To keep things simple, I'll assume for now that the rows we are checking all say "Summary:" in column A, and no other rows say "Summary:" in column A.
Finally, do we know how many rows there are in the worksheet that contain data? Again, to keep things simple, I'll assume we don't know how many rows there are but we do know that every row contains a value in column B.

So, based on my assumptions, here's what I would do:

Since you want the code to run each time the worksheet recalculates, then you want to write code for the Calculate Event inside the worksheet module:

Private Sub Worksheet_Calculate()
Dim iLastRow As Long
Dim iRow As Long
Dim iCol As Long
Dim fHideThisRow as Boolean

iLastRow = Cells(Rows.Count, 2).End(xlUp).Row ' This will tell you the last row filled in column B, identified as 2 in the code.

For iRow = 1 to iLastRow
If Cells(iRow, 1) = "Summary:" Then ' Checking to see if it says "Summary:" in column A, aka 1.
fHideThisRow = True
For iCol = 2 to 6 ' Columns B through F
If Cells(iRow, iCol) <> 0 Then
fHideThisRow = False
Exit For
End If
Next iCol

If fHideThisRow = True Then
Rows(iRow).Hide
End If
End If
Next iRow
End Sub

Hope that helps.

BTW, I learned to program by visiting discussion groups like this; although it was dialup Compuserve back then. I made it a point that every time I asked a question on the forum, I would be sure to answer two others. 20 some years later, I still follow the same practice and I always learn just as much by answering questions as I do by asking them.

 

[Joe VROD]

Thank you for both the advice and assistance.


To answer your questions:
The numbers can not be negative.
The rows to check will always be the same.
The rows don't say Summary. Each of the rows say something different.
There are a large number of rows in the worksheet containing data. I have
two lookup tables and close to 300 rows of data. The summary portion which
is based on the values in the tables determined by variables entered in
another worksheet, is the portion that I am trying to control.

The summary area that I wish to print is made up of 56 rows and 7 columns,
many of which are blank for a cleaner look. The first several at the top
are simply headings. Column A is used for the definitions of the data in
the row (i.e. Anticipated Cash Inflow). Columns B-F represent Years 1-5.

The rows that I want to hide, based on a calculation that the sum of the
values in the row =0 are as follows:
- Variable cost item which is only triggered when a combination of factors
entered on the Input sheet matched against the organization type trigger a
formula which pulls the data from the lookup table and fills it in the row.
This applies to two of the rows in the summary section, each having
different inputs and formulas.
- Another variable cost item which is triggered simply by entering data in
an optional section of the input worksheet. This applies to two rows.
- The sum row which adds the two rows just described.

My purpose in hiding the rows is that the language used to define them can
cause confusion to customers that are not of the correct organization type.
It invites questions that take away from the simplicity of the data for most
customers.

Hope this helps. Again, I appreciate your assistance.