Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Excel 2007 to hide rows based on sum of several rows not ina range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Excel 2007 to hide rows based on sum of several rows not in a range
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 Gardill" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
Macro to show/hide rows based on a range of dates | Excel Programming | |||
hide multiple rows based on condition within rows | Excel Programming | |||
Is there a way to hide worksheets and/or rows/columns based on information enter into a particular cell of range of cells? | New Users to Excel | |||
Code to hide rows based on cell contents | Excel Programming |